이런 형식의 잔액(누계)를 구할수 있을까요 0 2 1,240

by 김학빈 [SQL Query] sql 누계 [2019.04.19 11:27:33]


통장 일자 입금 출금  잔액
기업은행 기초 180,000 80,000 100,000
  2019.03.01 10,000   110,000
  2019.03.10 20,000   130,000
  2019.03.20 50,000   180,000
  월계 80,000 0  
  누계 260,000 80,000 180,000
  2019.04.02 10,000 20,000 170,000
  2019.04.11 50,000 100,000 120,000
  2019.04.15 20,000 0 140,000
  월계 80,000 120,000  
  누계 340,000 200,000 140,000
우리은행 기초 100,000 80,000 20,000
  2019.03.01 10,000   30,000
  2019.03.10 20,000   50,000
  2019.03.20 50,000   100,000
  월계 80,000 0  
  누계 180,000 80,000 100,000
  2019.04.02 10,000 20,000 90,000
  2019.04.11 50,000 100,000 40,000
  2019.04.15 20,000 0 60,000
  월계 80,000 120,000  
  누계 260,000 200,000 60,000

이런형식의 잔액(누계)를 구하고 싶은데 어떻게  해야할까요

부디 도움을 주시길 부탁드립니다

by 신이만든지기 [2019.04.19 15:53:06]

가능해보입니다.

소스데이터를 알려주시면, 쿼리까지 작성가능 할 것 같네요.


by 이준환 [2019.04.19 17:05:58]
/*
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'
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입