Oracle - 순차적으로 차감하기2

[ Oracle - 순차적으로 차감하기2 ]

 

--stock_onhand : 현 재고 --item_Demand : 요청 수량 WITH stock_onhand AS ( SELECT '사과' AS item, 60 qty FROM dual UNION ALL SELECT '배' , 800 FROM dual UNION ALL SELECT '귤' , 800 FROM dual ) , item_Demand AS ( SELECT '사과' item, '20131001_1' demand_seq, 20 qty FROM dual UNION ALL SELECT '사과' , '20131003_1' , 50 FROM dual UNION ALL SELECT '배' , '20131003_2' , 80 FROM dual UNION ALL SELECT '배' , '20131001_1' , 200 FROM dual UNION ALL SELECT '배' , '20131007_1' , 300 FROM dual UNION ALL SELECT '포도' , '20131007_1' , 300 FROM dua ) SELECT NVL(A.item, B.item) AS item , A.qty 재고수량, B.요청일, B.요청수량, B.요청수량합계 , NVL(A.qty, 0) - NVL(B.요청수량합계, 0) AS 잔량1 , GREATEST(0, A.qty - B.요청수량합계) AS 잔량2 , LEAST(A.qty, B.요청수량합계) - (B.요청수량합계 - B.요청수량) AS 잔량3 FROM stock_onhand A FULL OUTER JOIN ( SELECT item, demand_seq 요청일, qty 요청수량 , SUM(qty) OVER(PARTITION BY item ORDER BY demand_seq) 요청수량합계 FROM item_Demand) B ON A.item = B.item ORDER BY A.item, B.요청일;