전월이월 데이터를 거래처당 한번씩만 더해주고싶습니다.
첫번째사진을 보시면 거래처-DDATE마다 한번씩 더해주고있는데
두번째사진처럼 거래처당 가장첫번째 잔액에만 전월이월데이터를 더해주고싶습니다.
IF CASE문으로 시도해보고있는데 잘 생각이 안나서 질문드립니다
고견 부탁드리겠습니다.
(현재 쿼리)
WITH COST_TAB AS ( SELECT 1 SEQ, '청과물상회' SHOP, '2020-01-01' DDATE, 300000 TT_COST UNION ALL SELECT 2 SEQ, '청과물상회' SHOP, '2020-01-01' DDATE, 180000 TT_COST UNION ALL SELECT 3 SEQ, '청과물상회' SHOP, '2020-02-01' DDATE, 200000 TT_COST UNION ALL SELECT 4 SEQ, '청과물상회' SHOP, '2020-02-01' DDATE, 80000 TT_COST UNION ALL SELECT 5 SEQ, '미용실' SHOP, '2020-01-01' DDATE, 225000 TT_COST UNION ALL SELECT 6 SEQ, '미용실' SHOP, '2020-01-01' DDATE, 175000 TT_COST UNION ALL SELECT 7 SEQ, '미용실' SHOP, '2020-02-01' DDATE, 125000 TT_COST UNION ALL SELECT 8 SEQ, '미용실' SHOP, '2020-02-01' DDATE, 275000 TT_COST ), BAL_TAB AS ( SELECT 1 SEQ, '청과물상회' SHOP,'[전월이월]' DDATE, 10000 BALANCE UNION ALL SELECT 2 SEQ, '미용실' SHOP,'[전월이월]' DDATE, 20000 BALANCE ) SELECT * FROM( SELECT SEQ, SHOP, DDATE, 0 AS TT_COST, BALANCE FROM BAL_TAB UNION ALL SELECT CT.SEQ, CT.SHOP, CT.DDATE, CT.TT_COST, BT.BALANCE + CT.SUM_TT AS BALANE FROM BAL_TAB BT ,( SELECT SEQ, SHOP, DDATE, TT_COST ,SUM(TT_COST)OVER(PARTITION BY SHOP,DDATE ORDER BY SEQ) SUM_TT FROM COST_TAB) CT WHERE CT.SHOP = BT.SHOP ) DD ORDER BY SHOP , DDATE , BALANCE
IF DDATE == '2020-10-01' THEN BT.BALANCE + CT.SUM_TT AS BALANE 이렇게 값을 정해놓지 않아야합니다. DDATE라는건 변하는 값이라...ㅠ
SELECT * FROM( SELECT SEQ, SHOP, DDATE, 0 AS TT_COST, BALANCE , 0 SUM_TT , 0 RN1 , 0 RN2 FROM BAL_TAB UNION ALL SELECT CT.SEQ, CT.SHOP, CT.DDATE, CT.TT_COST , CASE WHEN RN1 = 1 THEN BT.BALANCE + CT.SUM_TT ELSE CT.SUM_TT END BALANE , CT.SUM_TT , RN1 , RN2 FROM BAL_TAB BT , ( SELECT SEQ, SHOP, DDATE, TT_COST , SUM(TT_COST) OVER (PARTITION BY SHOP, DDATE ORDER BY SEQ) SUM_TT , RANK() OVER (PARTITION BY SHOP ORDER BY SHOP, DDATE) RN1 , ROW_NUMBER() OVER (PARTITION BY SHOP, DDATE ORDER BY SHOP, DDATE, SEQ) RN2 FROM COST_TAB ) CT WHERE CT.SHOP = BT.SHOP ) DD ORDER BY SHOP, RN1, DDATE, RN2 ; 그런데 전월이월 테이블이 따로 있는건가요??
저는 약간 끼워맞춘 느낌이긴 합니다만..^^;
SELECT SEQ, SHOP, DDATE, TT_COST, COALESCE(SUM(BALANCE) OVER (PARTITION BY SHOP,RN ORDER BY SEQ,DDATE),BALANCE) FROM (SELECT SEQ, SHOP, DDATE, 0 AS TT_COST, BALANCE, 1 AS RN FROM BAL_TAB UNION ALL SELECT SEQ, SHOP, DDATE, TT_COST, TT_COST, RANK() OVER (PARTITION BY SHOP ORDER BY DDATE) FROM COST_TAB) AS t