회계 쿼리 (일계, 월계, 누계 구하기)

[원쿼리맨]

이러한 입출금 테이블이 하나있습니다. 이테이블을 가지고 현금출납장을 만드려고 합니다.

ct_dt	ta_cd	in	out
20050101	미지급금	10,000	0
20050101	예금	0	10,000
20050201	급여	0	50,000
20050201	수입	10,000	0
20050201	상여	0	60,000
20050220	급여	0	20,000
20050220	상여	0	20,000
:	:	:	:

결과는

ct_dt	ta_cd	in	out
20050101	미지급금	10,000	0
20050101	급여	0	10,000
	일계	10,000	10,000
	월계	10,000	10,000
	누계	10,000	10,000
20050201	급여	0	50,000
20050201	수입	10,000	0
20050201	상여	0	60,000
	일계	10,000	110,000
	누계	20,000	120,000
20050220	급여	0	20,000
20050220	상여	0	20,000
	일계	0	40,000
	월계	0	150,000
	누계	20,000	160,000
:	:	:	:
:	:	:	:

이러한 결과물을 쿼리로 만드려고합니다.

해당일의 마지막에는 일계와, 누계가 나와야하며

달의 마지막 일에는 일계, 월계, 누계가 나와야합니다.

---------------------------------------------------------------

가다듬을 여지는 많지만, 쿼리는 .......

SELECT DECODE (ta_cd, NULL, NULL, ct_dt) ct_dt,
       DECODE (gid + sum_gid, 4, ta_cd, 5, '일계', 7, '누계',
               11, ta_cd, 12, '일계', 14, '월계', 18, '누계') ta_cd,
       DECODE (gid + sum_gid, 4, ins, 5, ins, 7, sum_ins_tot,
               11, ins, 12, ins, 14, sum_ins_mon, 18, sum_ins_tot) ins,
       DECODE (gid + sum_gid, 4, outs, 5, outs, 7, sum_outs_tot,
         11, outs, 12, outs, 14, sum_outs_mon, 18, sum_outs_tot) outs
  FROM (SELECT   ct_dt, ta_cd, SUM (ins) ins, SUM (outs) outs,
                 GROUPING_ID (SUBSTR (ct_dt, 1, 6), ct_dt, 1, ta_cd) gid,
                 SUM (ins) OVER (ORDER BY SUBSTR (ct_dt, 1, 6),
              ct_dt, 1, ta_cd, ins, outs, ROWNUM) sum_ins_tot,
                 SUM (outs) OVER (ORDER BY SUBSTR (ct_dt, 1, 6),
                         ct_dt, 1, ta_cd, ins, outs, ROWNUM) sum_outs_tot,
                 SUM (ins) OVER (PARTITION BY SUBSTR (ct_dt, 1, 6)) sum_ins_mon,
                 SUM (outs) OVER (PARTITION BY SUBSTR (ct_dt, 1, 6)) sum_outs_mon,
                 SUM (GROUPING_ID (SUBSTR (ct_dt, 1, 6), ct_dt, 1, ta_cd)
                     ) OVER (PARTITION BY MAX (ct_dt)) sum_gid
            FROM TEST
        GROUP BY ROLLUP (SUBSTR (ct_dt, 1, 6), ct_dt, 1,
                         (ta_cd, ins, outs, ROWNUM))
          HAVING GROUPING_ID (SUBSTR (ct_dt, 1, 6), ct_dt, 1, ta_cd) <> 15)

참고로 TEST 테이블 생성 스크립트는 아래와 같습니다.

CREATE TABLE TEST AS
SELECT     TO_CHAR (  TO_DATE ('20050101', 'yyyymmdd')
                    + ROUND (DBMS_RANDOM.VALUE (0, 58)),
                    'yyyymmdd'
                   ) ct_dt,
           '급여' ta_cd, 1000 * ROUND (DBMS_RANDOM.VALUE (0, 10)) ins,
           1000 * ROUND (DBMS_RANDOM.VALUE (0, 10)) outs
      FROM DUAL
CONNECT BY LEVEL <= 100