데이터가 아래와 같이 들어가 있습니다.
A 250 20190101
A 280 20190102
A 110 20190103
A 200 20190104
A 150 20190105
A 100 20190106
이중에 제가 A 아이템에 대해서 특정 수량만큼 요청이 되었을 때 (A 300)
날짜 순으로 A 250 짜리와 A 280 짜리만 조회되게 할 수 있을까요?
A 300이 요청인데 A 250이 선택되고 나머지 50이 다음것 A 280짜리에 선택되어서 2건이 조회입니다.
A가 550이 요청되면
A 250
A 280
A 110
이렇게 3개가 조회되어야 됩니다.
WITH T (ITEM , QTY , DT ) AS ( SELECT 'A', 250 , '20190101' FROM DUAL UNION ALL SELECT 'A', 280 , '20190102' FROM DUAL UNION ALL SELECT 'A', 110 , '20190103' FROM DUAL UNION ALL SELECT 'A', 200 , '20190104' FROM DUAL UNION ALL SELECT 'A', 150 , '20190105' FROM DUAL UNION ALL SELECT 'A', 100 , '20190106' FROM DUAL ) SELECT * FROM (SELECT A.* , SUM(CASE WHEN SUM_QTY >= 550 THEN 1 ELSE 0 END ) OVER(PARTITION BY ITEM ORDER BY DT ) RN FROM (SELECT T.* ,SUM(QTY) OVER(PARTITION BY ITEM ORDER BY DT ) SUM_QTY FROM T ) A ) WHERE RN <= 1
WITH t AS ( SELECT 'A' item, 250 qty, '20190101' dt FROM dual UNION ALL SELECT 'A', 280, '20190102' FROM dual UNION ALL SELECT 'A', 110, '20190103' FROM dual UNION ALL SELECT 'A', 200, '20190104' FROM dual UNION ALL SELECT 'A', 150, '20190105' FROM dual UNION ALL SELECT 'A', 100, '20190106' FROM dual ) SELECT a.item , a.qty , a.dt , a.s_qty , b.req_qty , LEAST(a.s_qty, b.req_qty) - (a.s_qty - a.qty) use_qty , a.qty - LEAST(a.s_qty, b.req_qty) + (a.s_qty - a.qty) rem_qty FROM (SELECT item, qty, dt , SUM(qty) OVER(PARTITION BY item ORDER BY dt) s_qty FROM t ) a , (SELECT 'A' item, 300 req_qty FROM dual) b WHERE a.item = b.item AND (a.s_qty - a.qty) < b.req_qty ;