입고요청 대비, 출고를 하지 않은 품목에 대해서 요청자에게 책임을 지게 하려고 합니다.
기준은 현재고를 기준으로 해서요..
원하는 DATA는 여기서 보이는 책임수량입니다.
입고대비 출고를 하지 않은 잔량이 50인데.. 현재고가 985 인데 그중 이사람의 책임은 50개이고.. 현재고 기준으로 잔량만큼을 빼서 딱 현재고만큼만 책임수량이
만들어지는 그런 쿼리를 만들고 싶습니다. 각 품목마다 현재고 기준으로 작업하는것이고.. 어떻게 쿼리를 짜야할까요? 감이 오질 않네요..
1 2 3 4 5 6 7 8 9 10 11 12 | WITH t AS ( SELECT 'A' AS item, '20170322' AS RDDATE, 50 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20170929' AS RDDATE, 300 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180212' AS RDDATE, 5 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180227' AS RDDATE, 1141 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180312' AS RDDATE, 2 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'B' AS item, '20170607' AS RDDATE, 10000 AS RCPTQTY, 100 AS ORDQTY, 25200 AS STOQTY FROM dual UNION ALL SELECT 'B' AS item, '20180327' AS RDDATE, 33000 AS RCPTQTY, 0 AS ORDQTY, 25200 AS STOQTY FROM dual ) SELECT * FROM T ORDER BY ITEM, RDDATE ; |
품목 | 요청자 | 입고일 | 입고요청 | 출고요청 | 잔량 | 현재고 | 책임수량 |
A | 담당자1 | 20170322 | 50 | 0 | 50 | 985 | 50 |
A | 담당자2 | 20170929 | 300 | 0 | 300 | 985 | 300 |
A | 담당자3 | 20180212 | 5 | 0 | 5 | 985 | 5 |
A | 담당자4 | 20180227 | 1,141 | 0 | 1,141 | 985 | 630 |
A | 담당자5 | 20180312 | 2 | 0 | 2 | 985 | 0 |
B | 담당자6 | 20170607 | 10,000 | 100 | 10,000 | 25,200 | 9,900 |
B | 담당자7 | 20180327 | 33,000 | 0 | 33,000 | 25,200 | 15,300 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH t AS ( SELECT 'A' item, '20170322' rddate, 50 rcptqty, 0 ordqty, 985 stoqty FROM dual UNION ALL SELECT 'A' , '20170929' , 300, 0, 985 FROM dual UNION ALL SELECT 'A' , '20180212' , 5, 0, 985 FROM dual UNION ALL SELECT 'A' , '20180227' , 1141, 0, 985 FROM dual UNION ALL SELECT 'A' , '20180312' , 2, 0, 985 FROM dual UNION ALL SELECT 'B' , '20170607' , 10000, 100, 25200 FROM dual UNION ALL SELECT 'B' , '20180327' , 33000, 0, 25200 FROM dual ) SELECT item, rddate, rcptqty, ordqty, stoqty , qty_r 잔량 , GREATEST(LEAST(qty_r, stoqty - qty_s + qty_r), 0) 책임수량 FROM ( SELECT item, rddate, rcptqty, ordqty, stoqty , rcptqty - ordqty qty_r , SUM (rcptqty - ordqty) OVER(PARTITION BY item ORDER BY rddate) qty_s FROM t ) ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | WITH t AS ( SELECT 'A' AS item, '20170322' AS RDDATE, 50 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20170929' AS RDDATE, 300 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180212' AS RDDATE, 5 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180227' AS RDDATE, 1141 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'A' AS item, '20180312' AS RDDATE, 2 AS RCPTQTY, 0 AS ORDQTY, 985 AS STOQTY FROM dual UNION ALL SELECT 'B' AS item, '20170607' AS RDDATE, 10000 AS RCPTQTY, 100 AS ORDQTY, 25200 AS STOQTY FROM dual UNION ALL SELECT 'B' AS item, '20180327' AS RDDATE, 33000 AS RCPTQTY, 0 AS ORDQTY, 25200 AS STOQTY FROM dual ) SELECT 품목 , 입고일 , 입고요청 , 출고요청 , 잔량 , 현재고 , CASE WHEN 잔여수량 > 잔량 THEN 잔량 - 출고요청 WHEN LAG(잔여수량,1) OVER(PARTITION BY 품목 ORDER BY 입고일 ) <= 0 THEN 0 ELSE LAG(잔여수량,1) OVER(PARTITION BY 품목 ORDER BY 입고일 ) END 책임수량 FROM ( SELECT ITEM AS "품목" , RDDATE AS "입고일" , RCPTQTY AS "입고요청" , ORDQTY AS "출고요청" , RCPTQTY AS "잔량" , STOQTY AS "현재고" , STOQTY - SUM (RCPTQTY) OVER(PARTITION BY ITEM ORDER BY RDDATE ) + SUM (ORDQTY) OVER(PARTITION BY ITEM ORDER BY RDDATE ) AS "잔여수량" FROM T ORDER BY ITEM, RDDATE ) |