Oracle - 순차적으로 누적하기4

[ Oracle - 순차적으로 누적하기4 ]

 

with t as(
select  '20120502' dat, '10001' lotno, 21120 inp_qty, 0 god_qty from dual union all
select  '20120505' dat, '10001' lotno, 21120 inp_qty, 7000 god_qty from dual union all
select  '20120507' dat, '10001' lotno, 0 inp_qty, 13000 god_qty from dual
)
select  * from t

DAT LOTNO    INP_QTY    GOD_QTY
-------------------------------------------------------------
20120502   10001   21120     0
20120505   10001   21120     7000 --> inp_qty - god_qty = 14120
20120507   10001   14120   13000    

 

 

WITH t1 AS ( SELECT '20120502' dat, '10001' lotno, 21120 inp_qty FROM dual ), t2 AS ( SELECT '20120505' dat, '10001' lotno, 7000 god_qty FROM dual UNION ALL SELECT '20120507' , '10001' , 13000 FROM dual ), t3 AS ( SELECT dat, lotno, inp_qty, 0 god_qty FROM t1 UNION ALL SELECT dat, lotno, 0 inp_qty, god_qty FROM t2 ) SELECT dat , lotno , (SELECT NVL(SUM(inp_qty - god_qty), 0) FROM t3 WHERE lotno = a.lotno AND dat < a.dat ) + inp_qty inp_qty_sum , god_qty FROM t3 a ORDER BY lotno, dat ;