이 글에 대한 댓글이 총 3건 있습니다.결과테이블 TC 를 사용할 경우
/* 코드/일자별 출고량 추출 */
for c1 in ( select code, reqdate, reqqty
from ta
order by code, reqdate ) loop
/* 추출한 출고량 */
wk_reqqty := c1.reqqty;
/* 추출한 코드의 출고일자 이전 출고량 계산 */
select sum(reqqty) into wk_preqty
from ta
where code = c1.code
and reqdate < c1.reqdate;
/* 추출한 코드에 대한 입고량 추출 */
for c2 in ( select code, inputdate, inventory
from tb
where code = c1.code
order by inputdate ) loop
/* 추출한 입고량 */
wk_inventory := c2.inventory;
/* 이전 출고량과 입고량 비교 */
if (wk_preqty > 0) then
if (wk_preqty >= wk_inventory) then
wk_preqty := wk_preqty - wk_inventory;
wk_inventory := 0;
else
wk_inventory := wk_inventory - wk_preqty;
wk_preqty := 0;
end if;
end if;
/* 이전 출고량 반영 후 입고량이 남은 경우 출고량 반영 */
if (wk_inventory > 0) then
if (wk_inventory >= wk_reqqty) then
insert into tc (reqdate, code, reqqty, inputdate, outqty)
values (c1.reqdate, c1.code, c1.reqqty, c2.inputdate, wk_reqqty);
exit;
else
insert into tc (reqdate, code, reqqty, inputdate, outqty)
values (c1.reqdate, c1.code, c1.reqqty, c2.inputdate, wk_inventory);
wk_reqqty := wk_reqqty - wk_inventory;
end if;
end if;
end loop; /* end of for c2 */
end loop; /* end of for c1 */
select * from tc
order by code, reqdate;
작업용 테이블을 사용하지 않고 하나의 쿼리로 해결하길 원하신건가요 ?
님의 쿼리도 올려주세요 ^^
임명순(a3sd)님이 2005-10-17 20:20:49에 작성한 댓글입니다.
이 댓글은 2005-10-17 20:22:30에 마지막으로 수정되었습니다.SELECT a.reqdate, a.code, a.reqqty, b.inputdate,
(CASE
WHEN a.bef >= b.bef AND a.bef < b.aft AND a.aft > b.aft
THEN b.aft - a.bef
WHEN a.aft > b.bef AND a.aft <= b.aft AND a.bef < b.bef
THEN a.aft - b.bef
WHEN a.bef < b.bef AND a.aft > b.aft
THEN b.inventory
WHEN a.bef >= b.bef AND a.aft <= b.aft
THEN a.reqqty
END
) outqty
FROM (SELECT reqdate, code, reqqty,
NVL
(SUM (reqqty) OVER (PARTITION BY code ORDER BY code,
reqdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
0
) bef,
SUM (reqqty) OVER (PARTITION BY code ORDER BY code,
reqdate) aft
FROM ta) a,
(SELECT inputdate, code, inventory,
NVL
(SUM (inventory) OVER (PARTITION BY code ORDER BY code,
inputdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
0
) bef,
SUM (inventory) OVER (PARTITION BY code ORDER BY code,
inputdate) aft
FROM tb) b
WHERE a.code = b.code
AND ( a.bef >= b.bef AND a.bef < b.aft AND a.aft > b.aft
OR a.aft > b.bef AND a.aft <= b.aft AND a.bef < b.bef
OR a.bef < b.bef AND a.aft > b.aft
OR a.bef >= b.bef AND a.aft <= b.aft
)
ORDER BY a.code, a.reqdate, b.inputdate
-- SQL 쿼리 질문은 SQL 까페에서... http://cafe.daum.net/oraclesqltuning
|
|
|
