|
도저히 해결 능력이 안되서 문의 드립니다.
전체 쿼리는 아래와 같습니다.
WITH XX AS (
SELECT RVC_GBN
, GRP_NM
, RVC_CD
, DET_NM
, FOOD
, BEVE
, PRODUCT
, TOT
, SUM(FOOD_COST) AS FOOD_COST
, SUM(BEVE_COST) AS BEVE_COST
, SUM(PRODUCT_COST) AS PRODUCT_COST
, SUM(FOOD_AVG) AS FOOD_AVG
, SUM(BEVE_AVG) AS BEVE_AVG
, SUM(PRODUCT_AVG) AS PRODUCT_AVG
, 0 AS FOOD_INOUT
, 0 AS BEVE_INOUT
, 0 AS PRODUCT_INOUT
, 0 AS FOOD_SOMO_INOUT
, 0 AS BEVE_SOMO_INOUT
, 0 AS PRODUCT_SOMO_INOUT
FROM(
SELECT C.CD_RVC_GBN AS RVC_GBN
, C.GRP_NM
, A.RVC_CD
, C.DET_NM
, SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) AS FOOD
, SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) AS BEVE
, SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) AS PRODUCT
, SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) + SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) + SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) AS TOT
, (CASE WHEN C.ITEM = '10' THEN C.COST1 ELSE 0 END) AS FOOD_COST
, (CASE WHEN C.ITEM = '20' THEN C.COST1 ELSE 0 END) AS BEVE_COST
, (CASE WHEN C.ITEM = '70' THEN C.COST1 ELSE 0 END) AS PRODUCT_COST
, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '10' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END))*100,1) AS FOOD_AVG
, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '20' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END))*100,1) AS BEVE_AVG
, ROUND(DECODE(SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END),0,0,(CASE WHEN C.ITEM = '70' THEN C.COST1 ELSE 0 END) / SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END))*100,1) AS PRODUCT_AVG
FROM MC_IF_SALES_CLOSE A
, (SELECT RVC_CD, ITEM, COST1, CD_RVC_GBN, BIZ_SL_CD, GRP_NM, DET_NM
FROM
(SELECT B.BIZ_SL_CD AS RVC_CD
, A.ITEM_GRP2 AS ITEM
, B.CD_RVC_GBN
, B.BIZ_SL_CD
, C.CD_NM_LO AS GRP_NM
, D.BIZ_SL_NM AS DET_NM
, SUM(A.SALES_USE_AMT) AS COST1
FROM MC_STOCK_MONTH A
, MM_MT_SL B
, MM_MT_SYS_CD C
, MM_MT_SL_SALES D
WHERE 1=1
AND A.BRANCH_CD = B.BRANCH_CD
AND A.STR_CD = B.STR_CD
AND B.BRANCH_CD = C.BRANCH_CD
AND B.CD_RVC_GBN = C.CD_ID
AND B.BIZ_SL_CD = D.BIZ_SL_CD
AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)
AND A.ITEM_GRP2 IN ('10','20','70')
AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')
AND A.STR_CD NOT IN ('HG08092','121157')
AND B.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')
AND A.BRANCH_CD = 'ICA'
GROUP BY B.STR_CD, A.ITEM_GRP2, B.CD_RVC_GBN, B.BIZ_SL_CD, C.CD_NM_LO, D.BIZ_SL_NM
UNION
SELECT B.BIZ_SL_CD AS RVC_CD
, A.ITEM_GRP2 AS ITEM
, B.CD_RVC_GBN
, B.BIZ_SL_CD
, E.CD_NM_LO AS GRP_NM
, F.BIZ_SL_NM AS DET_NM
, SUM(D.AMT) AS COST1
FROM MC_STOCK_MONTH A
, MM_MT_SL B
, MC_ITEM_MOVE D
, MM_MT_SYS_CD E
, MM_MT_SL_SALES F
WHERE A.BRANCH_CD = B.BRANCH_CD
AND A.STR_CD = B.STR_CD
AND B.BRANCH_CD = E.BRANCH_CD
AND B.CD_RVC_GBN = E.CD_ID
AND B.BIZ_SL_CD = F.BIZ_SL_CD
AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)
AND A.ITEM_GRP2 IN ('10','20','70')
AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')
AND A.STR_CD NOT IN ('HG08092','121157')
AND B.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')
AND D.MTRL_CD = A.MTRL_CD(+)
AND D.OUT_STR_CD = A.STR_CD(+)
AND D.ORDER_SLIP_GBN IS NOT NULL
AND A.BRANCH_CD = 'ICA'
AND D.BRANCH_CD = A.BRANCH_CD AND MOVE_DT LIKE SUBSTR('20170801', 1, 6)||'%'
GROUP BY B.STR_CD, A.ITEM_GRP2, B.CD_RVC_GBN, B.BIZ_SL_CD, E.CD_NM_LO, F.BIZ_SL_NM)) C
WHERE 1=1
AND A.RVC_CD = C.RVC_CD(+)
AND (A.SALES_GBN = '01' OR A.SALES_GBN = '02' OR A.SALES_GBN = '04')
AND A.BRANCH_CD = 'ICA'
AND A.SELL_DT BETWEEN '20170801' AND '20170831'
GROUP BY C.CD_RVC_GBN, C.GRP_NM,A.RVC_CD,C.DET_NM,C.ITEM,C.COST1
HAVING SUM(CASE WHEN SALES_GBN = '01' THEN SELL_AMT ELSE 0 END) > 0
OR SUM(CASE WHEN SALES_GBN = '02' THEN SELL_AMT ELSE 0 END) > 0
OR SUM(CASE WHEN SALES_GBN = '04' THEN SELL_AMT ELSE 0 END) > 0
)
GROUP BY RVC_GBN,GRP_NM,RVC_CD,DET_NM,FOOD,BEVE,PRODUCT,TOT
UNION ALL
SELECT RVC_GBN
, GRP_NM
, RVC_CD
, DET_NM
, 0,0,0,0,0,0,0,0,0,0
, SUM(FOOD_INOUT) AS FOOD_INOUT
, SUM(BEVE_INOUT) AS BEVE_INOUT
, SUM(PRODUCT_INOUT) AS PRODUCT_INOUT
, SUM(FOOD_SOMO_INOUT) AS FOOD_SOMO_INOUT
, SUM(BEVE_SOMO_INOUT) AS BEVE_SOMO_INOUT
, SUM(PRODUCT_SOMO_INOUT) AS PRODUCT_SOMO_INOUT
FROM (
SELECT B.REF_V01 AS RVC_GBN,B.GRP_NM,B.RVC_CD,B.DET_NM,
CASE WHEN A.ITEM_GRP2 = '10' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS FOOD_INOUT,
CASE WHEN A.ITEM_GRP2 = '20' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS BEVE_INOUT,
CASE WHEN A.ITEM_GRP2 = '70' THEN SUM(A.BUY_IN_AMT + A.STR_IN_AMT + A.MOVE_IN_AMT - A.BUY_OUT_AMT - A.MOVE_OUT_AMT - A.STR_OUT_AMT - A.INHOUSE_USE_AMT) ELSE 0 END AS PRODUCT_INOUT,
CASE WHEN A.ITEM_GRP2 = '10' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS FOOD_SOMO_INOUT,
CASE WHEN A.ITEM_GRP2 = '20' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS BEVE_SOMO_INOUT,
CASE WHEN A.ITEM_GRP2 = '70' THEN SUM(A.SALES_USE_AMT + A.SLIP_USE_AMT + A.INHOUSE_USE_AMT + A.LOSS_USE_AMT) ELSE 0 END AS PRODUCT_SOMO_INOUT
FROM MC_STOCK_MONTH A
, (SELECT MMS.BRANCH_CD, MMS.STR_CD, MSC.CD_ID DET_CD, MMS.BIZ_SL_CD RVC_CD, MMSS.BIZ_SL_NM DET_NM
, MMS.CD_RVC_GBN REF_V01
, MSC.CD_NM_LO GRP_NM
FROM MM_MT_SL MMS
, MM_MT_SYS_CD MSC
, MM_MT_SL_SALES MMSS
WHERE MMS.CD_RVC_GBN = MSC.CD_ID
AND MMS.BRANCH_CD = MSC.BRANCH_CD
AND MMS.BIZ_SL_CD = MMSS.BIZ_SL_CD
AND MMS.INT_SL_TYPE_CD2 IN ('MC1303','MC1304','MC1302')
AND MMS.BRANCH_CD = 'ICA') B
WHERE A.BRANCH_CD = B.BRANCH_CD
AND A.STR_CD = B.STR_CD
AND A.CLOSE_MM = SUBSTR('20170801', 1, 6)
AND A.ITEM_GRP2 IN ('10','20','70')
AND (A.ITEM_GRP2 = '10' OR A.ITEM_GRP2 = '20' OR A.ITEM_GRP2 = '70')
AND A.STR_CD NOT IN ('HG08092','121157')
AND A.BRANCH_CD = 'ICA'
GROUP BY B.REF_V01,B.GRP_NM,B.RVC_CD,B.DET_NM,A.ITEM_GRP2
)
GROUP BY RVC_GBN,GRP_NM,RVC_CD,DET_NM
)
SELECT
RVC_GBN
, GRP_NM
, RVC_CD
, CASE GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM)
WHEN 3 THEN '계'
WHEN 15 THEN '합계'
ELSE DET_NM
END AS DET_NM
, SUM(TOT) AS TOT
, SUM(FOOD_COST + BEVE_COST + PRODUCT_COST) AS M_COST
, ROUND(DECODE(SUM(TOT),0,0, (SUM(FOOD_COST + BEVE_COST + PRODUCT_COST) / SUM(TOT)) * 100),1) AS M_AVG
, SUM(FOOD_INOUT+BEVE_INOUT+PRODUCT_INOUT) AS INOUT
, ROUND(DECODE(SUM(TOT),0,0, (SUM(FOOD_INOUT+BEVE_INOUT+PRODUCT_INOUT) / SUM(TOT))*100),1) AS INPUT_AVG
, SUM(FOOD_SOMO_INOUT+BEVE_SOMO_INOUT+PRODUCT_SOMO_INOUT) AS SOMO_INOUT
, GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM) AS GP_ID
FROM XX
WHERE RVC_GBN NOT IN ('MC1201','MC1207')
GROUP BY ROLLUP(RVC_GBN,GRP_NM,RVC_CD,DET_NM)
HAVING GROUPING_ID(RVC_GBN,GRP_NM,RVC_CD,DET_NM) NOT IN (1,7)
ORDER BY RVC_GBN,RVC_CD
위 쿼리 실행시 나오는 화면입니다.
MC1202 B/Q 33 Ba 3927279 822196 20.9 0 0 0 0
MC1202 B/Q 34 Wed 19800000 5866298 29.6 0 0 0 0
MC1202 B/Q 계 23727279 6688494 28.2 0 0 0 3
MC1203 F/B 20 In 20388940 11294431 55.4 0 0 0 0
MC1203 F/B 21 On 152551219 252223692 165.3 -12547 0 -16720 0
MC1203 F/B 22 La 26482334 6914122 26.1 0 0 0 0
MC1203 F/B 24 Ra 29878337 9452138 31.6 0 0 0 0
MC1203 F/B 25 Ru 10080516 2871273 28.5 0 0 0 0
MC1203 F/B 26 Lo 13459001 1597204 11.9 0 0 0 0
MC1203 F/B 27 Garden 12377088 2918324 23.6 0 0 0 0
MC1203 F/B 41 Club 280000 131801 47.1 0 0 0 0
MC1203 F/B 101 Bar 5917617 1059982 17.9 0 0 0 0
MC1203 F/B 102 The 69090 812970 1176.7 0 0 0 0
MC1203 F/B 계 271484142 289275937 106.6 -12547 0 -16720 3
소계 295211421 (MC1202+MC1203)
MC1204 L/S 50 Lei 52690963 8809910 16.7 0 0 -1251 0
MC1204 L/S 53 Ten 265445 30048 11.3 0 0 0 0
MC1204 L/S 계 52956408 8839958 16.7 0 0 -1251 3
합계 348167829 304804389 87.5 -12547 0 -17971 15
문의내용 : 전체 조회 결과에서 MC1202 값에 계와 , MC1203 값에 계를 합해서 MC1203 밑에 소계를 추가하고 싶습니다.
방법이 있을까요 부탁드립니다.
|
