데이터베이스 질문과 답변

순차차감 로직의 sql을 만들려합니다. 0 5 1,903

by 느림보 [SQL Query] [2022.05.17 14:48:39]

재고 추적을 위해서, 순차차감 하여 일자 및 수량을 체크 하려 합니다. 

도저히 모르겠네요 ㅠㅠ 도와주세요 

사원명[emp] 품목[item] 날짜[reqdate] 입고[pqty] 출고[qty]
김지은 AA1 20210101 100 　
김지은 AA1 20210115 　 50
김지은 AA1 20210128 100 　
김지은 AA1 20210205 　 50
김지은 AA1 20210215 　 80
김지은 AA1 20210303 50 　
김지은 AA1 20210401 　 50
김지은 AA1 20210420 100 　
김지은 AA1 20210504 　 50
합계 350

280

 

위의 표를 기준으로  담당자별, 품목별, 입고일자 기준으로 순차 수량 차감하려고 합니다. 

최종 잔량으로 표시는 기간내에서 입고 SUM - 출고SUM 해서  잔량 70이 나오면서, 

잔량일자를 표시하고 싶은겁니다.  순차적으로 날짜별로 입고수량대비 출고수량을 카운트해서..  

 

최종잔량일자를 20210303 이 DATA로 구해지고, 잔량은 70으로 나왔으면 하다는겁니다. 

빠른일자 기준부터 입고에서 출고수량을 순차적으로 차감했을때 잔량이 남아지게 되는 그 일자를 구하고 싶습니다. 

아 설명도 어렵네요.. ㅠ  

제가 잘 설명을 한건지... 에고.. 도와주세요 

 

by 마농 [2022.05.17 14:57:31]

결과는 (사원, 품목별) 1줄만 나오면 되는 건가요?
왜? 3월3일이죠? 4월20일이 아니라?
DB 종류 및 버전은 뭔가요?

by 느림보 [2022.05.17 15:01:38]

네 역시 마농님 ㅠㅠ  결과는 1줄만 나오면 됩니다. 

DB는 MSSQL 2016입니다. 

 

by 마농 [2022.05.17 15:18:39]
?
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
WITH t AS
(
SELECT '김지은' emp, 'AA1' item, '20210101' reqdate, 100 pqty, null qty
UNION ALL SELECT '김지은', 'AA1', '20210115', null,   50
UNION ALL SELECT '김지은', 'AA1', '20210128',  100, null
UNION ALL SELECT '김지은', 'AA1', '20210205', null,   50
UNION ALL SELECT '김지은', 'AA1', '20210215', null,   80
UNION ALL SELECT '김지은', 'AA1', '20210303',   50, null
UNION ALL SELECT '김지은', 'AA1', '20210401', null,   50
UNION ALL SELECT '김지은', 'AA1', '20210420',  100, null
UNION ALL SELECT '김지은', 'AA1', '20210504', null,   50
)
SELECT emp
     , item
     , reqdate
     , sum_pqty
     , sum_qty
     , sum_pqty - sum_qty rem_qty
  FROM (SELECT emp, item, reqdate, pqty, qty
             , SUM(pqty) OVER(PARTITION BY emp, item ORDER BY reqdate) x
             , SUM(pqty) OVER(PARTITION BY emp, item) sum_pqty
             , SUM( qty) OVER(PARTITION BY emp, item) sum_qty
          FROM t
        ) a
-- WHERE sum_qty >= x - sum_pqty
 WHERE sum_qty >= x - pqty
   AND sum_qty <  x
   AND pqty IS NOT NULL
;

 

by 느림보 [2022.05.18 14:13:51]

WITH t AS
(
SELECT '김지은' emp, 'AA1' item, '20210102' reqdate, 100 pqty, null qty
UNION ALL SELECT '김지은', 'AA1', '20210106', null,   100
UNION ALL SELECT '김지은', 'AA1', '20210128',  100, null
UNION ALL SELECT '김지은', 'AA1', '20210205', null,   99
UNION ALL SELECT '김지은', 'AA1', '20210515', 500,   null

UNION ALL SELECT '강성', 'AB1', '20210106', 100,   null
UNION ALL SELECT '강성', 'AB1', '20210128', null , 100
UNION ALL SELECT '강성', 'AC1', '20210428', 100 , null
UNION ALL SELECT '강성', 'AC1', '20210528', null , 50


UNION ALL SELECT '강민', 'A91', '20210205', 100,   0
UNION ALL SELECT '강민', 'A91', '20210515', 500,   0

)
SELECT emp
     , item
     , reqdate
     , sum_pqty
     , sum_qty
     , sum_pqty - sum_qty rem_qty
  FROM (SELECT emp, item, reqdate, pqty, qty
             , SUM(pqty) OVER(PARTITION BY emp, item ORDER BY reqdate) x
             , SUM(pqty) OVER(PARTITION BY emp, item) sum_pqty
             , SUM( qty) OVER(PARTITION BY emp, item) sum_qty
          FROM t
        ) a
 WHERE sum_qty >= x - sum_pqty
   AND sum_qty <  x
   AND pqty IS NOT NULL


이럴경우에는 

김지은    AA1    20210128    700    199    501
강성    AC1    20210428    100    50    50
강민    A91    20210205    600    0    600

이런 DATA값이 나와야할거 같은데..

김지은, 강민이 2 ROW 로 나오네요 ㅠ 
이럴경우는 어떻게 수정해야할까요? 

by 마농 [2022.05.18 14:22:55]

편집하다가 실수가 있었네요. 정정했습니다.
- 오류 : WHERE sum_qty >= x - sum_pqty
- 수정 : WHERE sum_qty >= x - pqty

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