농부지기

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

WITH t AS ( SELECT '20120101' DT, 1 seq, 100 amt, 10 stdAmt FROM dual UNION ALL SELECT '20120102', 1, 200, 0 FROM dual UNION ALL SELECT '20120103', 1, 300, 0 FROM dual UNION ALL SELECT '20120104', 1,400, 0 FROM dual UNION ALL SELECT '20120105', 1,500, 0 FROM dual UNION ALL SELECT '20120106', 1,600, 0 FROM dual UNION ALL SELECT '20120107', 1,700, 0 FROM dual UNION ALL SELECT '20120101', 2, 100, 10 FROM dual UNION ALL SELECT '20120102', 2, 200, 0 FROM dual UNION ALL SELECT '20120103', 2, 300, 0 FROM dual UNION ALL SELECT '20120104', 2, 400, 0 FROM dual UNION ALL SELECT '20120105', 2, 500, 0 FROM dual UNION ALL SELECT '20120106', 2, 600, 0 FROM dual UNION ALL SELECT '20120107', 2, 700, 0 FROM dual ) SELECT DT , SEQ , amt , stdAmt , SUM(amt+stdAmt) OVER (PARTITION BY SEQ ORDER BY DT) sum_amt FROM t

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

WITH 재고테이블 AS ( SELECT 'PRO' product, 150 qty FROM dual ) , 출하테이블 AS ( SELECT '2010-06-20' dt,'PRO' product, 100 qty FROM dual UNION ALL SELECT '2010-06-24', 'PRO', 80 FROM dual UNION ALL SELECT '2010-06-28', 'PRO', 70 FROM dual ) SELECT b.dt, b.product, b.qty , a.qty - b.s_qty 재고 FROM 재고테이블 a, (SELECT dt, product, qty , SUM(qty) OVER(PARTITION BY product ORDER BY dt) s_qty FROM 출하테이블 ) b WHERE a.product(+) = b.product AND a.qty(+) > b.s_qty - b.qty ;

[ 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.요청일;