|
이런형식의 잔액(누계)를 구하고 싶은데 어떻게 해야할까요
부디 도움을 주시길 부탁드립니다
/* 2019.04.19, Jun H. Lee */ WITH T(ACC_NM, TRD_DY, DEPOS_AMT, WITHD_AMT) AS ( SELECT '기업은행', '0000.00.00', 180000, 80000 FROM DUAL UNION ALL SELECT '기업은행', '2019.03.01', 10000, 0 FROM DUAL UNION ALL SELECT '기업은행', '2019.03.10', 20000, 0 FROM DUAL UNION ALL SELECT '기업은행', '2019.03.20', 50000, 0 FROM DUAL UNION ALL SELECT '기업은행', '2019.04.02', 10000, 20000 FROM DUAL UNION ALL SELECT '기업은행', '2019.04.11', 50000, 100000 FROM DUAL UNION ALL SELECT '기업은행', '2019.04.15', 20000, 0 FROM DUAL UNION ALL SELECT '우리은행', '0000.00.00', 100000, 80000 FROM DUAL UNION ALL SELECT '우리은행', '2019.03.01', 10000, 0 FROM DUAL UNION ALL SELECT '우리은행', '2019.03.10', 20000, 0 FROM DUAL UNION ALL SELECT '우리은행', '2019.03.20', 50000, 0 FROM DUAL UNION ALL SELECT '우리은행', '2019.04.02', 10000, 20000 FROM DUAL UNION ALL SELECT '우리은행', '2019.04.11', 50000, 100000 FROM DUAL UNION ALL SELECT '우리은행', '2019.04.15', 20000, 0 FROM DUAL ) SELECT ACC_NM , NVL(TRD_DY, NVL2(K2, '월', '누')||'계') AS TRD_DY , NVL2(K2||TRD_DY, SUM(DEPOS_AMT), MAX(SUM_DEPOS_AMT)KEEP(DENSE_RANK LAST ORDER BY TRD_DY)) AS DEPOS_AMT , NVL2(K2||TRD_DY, SUM(WITHD_AMT), MAX(SUM_WITHD_AMT)KEEP(DENSE_RANK LAST ORDER BY TRD_DY)) AS WITHD_AMT , NVL2(TRD_DY||NVL2(K2, NULL, 'X'), MAX(+SUM_DEPOS_AMT -SUM_WITHD_AMT)KEEP(DENSE_RANK LAST ORDER BY TRD_DY), NULL) AS SUM_BALAN_AMT FROM ( SELECT ACC_NM , TRD_DY , DEPOS_AMT , WITHD_AMT , SUM(DEPOS_AMT)OVER(PARTITION BY ACC_NM ORDER BY TRD_DY) AS SUM_DEPOS_AMT , SUM(WITHD_AMT)OVER(PARTITION BY ACC_NM ORDER BY TRD_DY) AS SUM_WITHD_AMT , SUBSTR(TRD_DY, 1, 7) AS K1 , SUBSTR(TRD_DY, 1, 8) AS K2 FROM T ) GROUP BY ACC_NM, ROLLUP(K1, K2, TRD_DY) HAVING K1||K2||TRD_DY IS NOT NULL AND K1||TRD_DY <> '0000.00' ;