데이터베이스 질문과 답변

순차 차감? 수량을 계산하려 하는데.. 0 2 909

by 느림보 [SQL Query] [2018.10.15 14:57:03]

입고요청 대비, 출고를 하지 않은 품목에 대해서 요청자에게 책임을 지게 하려고 합니다.

기준은 현재고를 기준으로 해서요..

원하는 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

 

by 마농 [2018.10.15 16:23:54]
?
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
        )
;

 

by 우리집아찌 [2018.10.15 17:38:53]
?
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 
        )

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입