with t(dt, tp, onhand, qty) as
(
select '20170115', 'IN' , 100, 110 from dual union all
select '20170116', 'OUT', 100, -50 from dual union all
select '20170208', 'IN' , 100, 30 from dual union all
select '20170225', 'OUT', 100, -50 from dual union all
select '20170228', 'OUT', 100, -33 from dual union all
select '20170313', 'IN' , 100, 15 from dual union all
select '20170323', 'OUT', 100, -7 from dual
)
select dt
,qty
,onhand + sum(qty) over(order by dt, qty desc) ov
from t
order by dt
조회결과를 보시면 아래와같이 나옵니다.
DT QTY ONHAND OV
20170115 110 100 210
20170116 -50 100 160
20170208 30 100 190
20170225 -50 100 140
20170228 -33 100 107
20170313 15 100 122
20170323 -7 100 115
이것은 누적으로 표현된 것이고, 여기서부터 막히게 되었습니다.
마지막라인이 최종적으로 남은 수량이 표시되는데요.
최종적으로 아래표처럼 남은 수량의 라인들만 보이게 하고 싶습니다.
설명을드리면, 기존수량이 100개가 있고, 일자별로 수량이 들어가고 나옵니다.
선입선출 개념으로 기존수량 100개 부터 먼저 빠져나가고,
100개가 전부 빠지게되면, 다음차례로 1/15일 들어온 수량이 빠져나가는 식 입니다.
쿼리로 구현이 가능할까요?
부족한내용은 댓글로 추가하겠습니다. 읽어주셔서 감사합니다.
DT QTY
20170115 70
20170208 30
20170313 15
모바일로 작성할수밖에 없어서 양해부탁드려요.
[ 기준쿼리 ]
with t(dt, onhand, qty) as
(
select '20170115', 30, 110 from dual union all
select '20170116', 30, -50 from dual union all
select '20170208', 30, 30 from dual union all
select '20170225', 30, -50 from dual union all
select '20170228', 30, -33 from dual union all
select '20170313', 30, 15 from dual union all
select '20170323', 30, -7 from dual
)
select dt
,onhand
,qty
from t
order by dt
[표설명]
발생일자 발생수량 최종수량 기존수량 기존수량+발생수량 선입선출 수량변화
DT QTY ONHAND T1 T2
20170115 110 30 15 125
20170116 -50 30 125 75 기존수량 15 소진 || 1/15 잔량 75
20170208 30 30 75 105 01/15 잔량 75 || 02/08 잔량 30
20170225 -50 30 105 55 01/15 잔량 25 || 02/08 잔량 30
20170228 -33 30 55 22 01/15 소진 25 || 02/08 잔량 22
20170313 15 30 22 37 02/08 잔량 22 || 03/13 잔량 15
20170323 -7 30 37 30 02/08 잔량 15 || 03/13 잔량 15
T1,2 는 흐름을 보여드리기 위해 작성한 부분입니다.
여기서 최종수량 30개를 역산해보면 쿼리에서 표현되지 않은 1월이전의 15개 수량이 존재하게 됩니다.
이 수량까지 감안해서 선입선출 개념을 적용해 기존수량을 순차적으로 빼면서
발생일자 기준으로 남아있는 수량을 표현하고 싶습니다.
[ 최종결과물 ]
DT QTY
20170208 15
20170313 15
최종수량이 별개의 라인으로 나오는게 편하시다면 이걸로 써도 될거 같습니다.
with t(dt, qty, remark) as
(
select '20170115', 110, '' from dual union all
select '20170116', -50, '' from dual union all
select '20170208', 30, '' from dual union all
select '20170225', -50, '' from dual union all
select '20170228', -33, '' from dual union all
select '20170313', 15, '' from dual union all
select '20170323', -7, '' from dual union all
select '49991231', 30, '최종수량' from dual
)
select dt
,qty
,remark
from t
order by dt
WITH PSI(DT, TP, ONH_QTY, QTY) AS ( SELECT '20170115', 'P', 30, 110 FROM DUAL UNION ALL SELECT '20170116', 'S', 30, 50 FROM DUAL UNION ALL SELECT '20170208', 'P', 30, 30 FROM DUAL UNION ALL SELECT '20170225', 'S', 30, 50 FROM DUAL UNION ALL SELECT '20170228', 'S', 30, 33 FROM DUAL UNION ALL SELECT '20170313', 'P', 30, 15 FROM DUAL UNION ALL SELECT '20170323', 'S', 30, 7 FROM DUAL ) SELECT A.GR_DT , A.GR_QTY - SUM(LEAST(NVL(LA.GI_EDQTY-A.GR_STQTY,0), NVL(A.GR_EDQTY-LA.GI_STQTY,0), A.GR_QTY, NVL(LA.GI_QTY,0))) AS REM_QTY FROM ( -- "입고 (일자별 입고수량 범위)" SELECT A.DT AS GR_DT, A.QTY AS GR_QTY , SUM(A.QTY) OVER (ORDER BY A.DT) - A.QTY AS GR_STQTY , SUM(A.QTY) OVER (ORDER BY A.DT) AS GR_EDQTY FROM ( -- 추가 입고분 SELECT '00000000' AS DT, 'I' AS TP, A.ONH_QTY - SUM(DECODE(A.TP,'P',A.QTY,'S',-A.QTY)) AS QTY FROM PSI A GROUP BY A.ONH_QTY UNION ALL -- 입고분 SELECT A.DT, A.TP, A.QTY FROM PSI A WHERE A.TP = 'P' ) A ) A LEFT OUTER JOIN ( -- "출고 (일자별 출고수량 범위)" SELECT A.DT AS GI_DT, A.QTY AS GI_QTY , SUM(A.QTY) OVER (ORDER BY A.DT) - A.QTY AS GI_STQTY , SUM(A.QTY) OVER (ORDER BY A.DT) AS GI_EDQTY FROM PSI A WHERE A.TP = 'S' ) LA ON A.GR_STQTY < LA.GI_EDQTY AND A.GR_EDQTY > LA.GI_STQTY GROUP BY A.GR_DT, A.GR_QTY ORDER BY 1 ;
-- 윗분들 말씀처럼 제가 문제를 잘 이해한지 모르겠지만 -- 마농님 부재를 틈타 올만에 급히 한번. with t# (dt, onhand, qty) as ( select '20170115', 30, 110 from dual union all select '20170116', 30, -50 from dual union all select '20170208', 30, 30 from dual union all select '20170225', 30, -50 from dual union all select '20170228', 30, -33 from dual union all select '20170313', 30, 15 from dual union all select '20170323', 30, -7 from dual ) ,t (dt, qty) as ( select '11111111', max(onhand)-sum(qty) from t# union all select dt, qty from t# ) select dt ,decode(row_number() over (order by dt),1,o_t_qty + i_s_qty,qty) qty from ( select dt ,qty ,sum(case when qty < 0 then qty end) over() o_t_qty ,sum(case when qty > 0 then qty end) over(order by dt) i_s_qty from t ) where o_t_qty + i_s_qty > 0 and qty > 0 ;
WITH t(dt, tp, onhand, qty) AS ( SELECT '20170115', 'IN' , 100, 110 FROM dual UNION ALL SELECT '20170116', 'OUT', 100, -50 FROM dual UNION ALL SELECT '20170208', 'IN' , 100, 30 FROM dual UNION ALL SELECT '20170225', 'OUT', 100, -50 FROM dual UNION ALL SELECT '20170228', 'OUT', 100, -33 FROM dual UNION ALL SELECT '20170313', 'IN' , 100, 15 FROM dual UNION ALL SELECT '20170323', 'OUT', 100, -7 FROM dual ) , t1 AS ( SELECT dt, tp, onhand , DECODE(ROW_NUMBER() OVER(ORDER BY dt), 1, onhand, 0) + ABS(qty) qty , DECODE(tp, 'IN', onhand, 0) + SUM(ABS(qty)) OVER(PARTITION BY tp ORDER BY dt) qty_s FROM t ) SELECT a.dt , a.qty - NVL(SUM(LEAST(a.qty, b.qty, b.qty_s - a.qty_s + a.qty, a.qty_s - b.qty_s + b.qty)), 0) r_qty FROM t1 a LEFT OUTER JOIN t1 b ON a.qty_s - a.qty < b.qty_s AND b.qty_s - b.qty < a.qty_s AND b.tp = 'OUT' WHERE a.tp = 'IN' GROUP BY a.dt, a.qty ORDER BY dt ;
최종수량(30)을 기준으로 일자별 남은수량 가져오는 경우라면.
기존 잔여수량(15)를 역산하거나 입출고를 짜맞출 필요 없이
입고내역만 가지고 최근일자로부터 최종수량(30)만 채우면 될 듯.
WITH t(dt, qty, remark) AS ( SELECT '20170115', 110, '' FROM dual UNION ALL SELECT '20170116', -50, '' FROM dual UNION ALL SELECT '20170208', 30, '' FROM dual UNION ALL SELECT '20170225', -50, '' FROM dual UNION ALL SELECT '20170228', -33, '' FROM dual UNION ALL SELECT '20170313', 15, '' FROM dual UNION ALL SELECT '20170323', -7, '' FROM dual UNION ALL SELECT '49991231', 30, '최종수량' FROM dual ) SELECT dt , CASE WHEN s_qty > r_qty THEN r_qty - s_qty + qty ELSE qty END qty FROM (SELECT dt, qty , SUM(DECODE(remark, '', qty)) OVER(ORDER BY dt DESC) s_qty , SUM(DECODE(remark, '최종수량', qty)) OVER() r_qty FROM t WHERE qty > 0 ) WHERE s_qty - qty < r_qty ORDER BY dt ;