누적차감에 대한 부분이 궁금합니다.
예를 들어 A ITEM 150 현재고 남은수량 50 일경우
현재고 | 수량 | 날짜 | |
A | 150 | 50 | 20180911 |
B | 200 | 90 | 20170101 |
B | 200 | 120 | 20180701 |
이 DATA를 기준으로 아래의 차감수량이 나왔으면 합니다.
아이템별 현재고에서 수량을 날짜기준으로 누적으로 빼주는 형식으로요.. 어떻게 해야할까요?
B의 경우 200-90을 빼고 차감에 110이 나왔는데.. 110에서 수량 120을 빼주어서 -10이 나오는 형태요
현재고 | 수량 | 날짜 | 차감 | |
A | 150 | 50 | 20180911 | 100 |
B | 200 | 90 | 20170101 | 110 |
B | 200 | 120 | 20180701 | -10 |
WITH t AS ( SELECT 'A' AS item, 150 AS onhand_qty, 50 AS out_qty, '20180911' AS dt, 1 seq FROM dual UNION ALL SELECT 'B' AS item, 200 AS onhand_qty, 90 AS out_qty, '20170101' AS dt, 2 seq FROM dual UNION ALL SELECT 'B' AS item, 200 AS onhand_qty, 120 AS out_qty, '20180701' AS dt, 3 seq FROM dual UNION ALL SELECT 'A' AS item, 170 AS onhand_qty, 40 AS out_qty, '20180912' AS dt, 4 seq FROM dual ) SELECT item, onhand_qty,out_qty, dt,onhand_qty - SUM(out_qty) OVER(PARTITION BY item ORDER BY dt, seq ) conv_qty FROM t ORDER BY seq
WITH t AS ( SELECT 'A' AS item, 1000 AS onhand_qty, 50 AS out_qty, 1 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 20 AS out_qty, 2 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 90 AS out_qty, 3 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 120 AS out_qty, 4 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 300 AS out_qty, 5 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 280 AS out_qty, 6 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 120 AS out_qty, 7 seq FROM dual ) SELECT item, onhand_qty,out_qty, onhand_qty - SUM(out_qty) OVER(PARTITION BY item ORDER BY ITEM, seq ) conv_qty FROM t ORDER BY item
이렇게 일경우 결과값이 이렇게인데.. 이중 B 의 conv_qty 가 0보다 작을경우
음수가 되기전까지만 차감되게 하고 싶습니다.
ITEM | onhand_qty | out_qty | conv_qty |
A | 1000 | 50 | 950 |
A | 1000 | 20 | 930 |
A | 1000 | 90 | 840 |
A | 1000 | 120 | 720 |
B | 500 | 300 | 200 |
B | 500 | 280 | -80 |
B | 500 | 120 |
-200 |
이렇게요... 혹시 이것도 가능할까요?
ITEM | onhand_qty | out_qty | conv_qty |
A | 1000 | 50 | 950 |
A | 1000 | 20 | 930 |
A | 1000 | 90 | 840 |
A | 1000 | 120 | 720 |
B | 500 | 300 | 200 |
B | 500 | 280 | 200 |
B | 500 | 120 | 0 |
select item, onhand_qty, out_qty, seq , decode(s1, 1, val, greatest(r1, s1)) as conv_qty from (select item, onhand_qty, out_qty, seq , val , decode(sign(val), 1, 1, 0) as s1 , lag(val, 1)over(partition by item order by seq) as r1 from (select item, onhand_qty,out_qty, seq , onhand_qty - sum(out_qty) over(partition by item order by seq ) val from t ) );
WITH t AS ( SELECT 'A' item, 1000 onhand_qty, 50 out_qty, '20180901' out_dt FROM dual UNION ALL SELECT 'A', 1000, 20, '20180902' FROM dual UNION ALL SELECT 'A', 1000, 90, '20180903' FROM dual UNION ALL SELECT 'A', 1000, 120, '20180904' FROM dual UNION ALL SELECT 'B', 500, 300, '20180901' FROM dual UNION ALL SELECT 'B', 500, 280, '20180902' FROM dual UNION ALL SELECT 'B', 500, 120, '20180903' FROM dual ) SELECT item, onhand_qty, out_qty, out_dt , GREATEST(onhand_qty - sum_qty, 0) conv_qty FROM (SELECT item, onhand_qty, out_qty, out_dt , SUM(out_qty) OVER(PARTITION BY item ORDER BY out_dt) sum_qty FROM t ) WHERE onhand_qty > sum_qty - out_qty ;