재고이월 후 기말재고 구하기 0 14 4,668

by 이정우 [SQL Query] 이월 기말 재고 [2024.04.02 12:52:04]


안녕 하세요. 

월       매장  전월기말  입고   출고   기말
202306   A      10        100   10     100  
202307   A     100        0     50     50 
202308   A      50        50    0      100
202309   A     100        10    100    10

With T_INV AS (
Select '202306' YYYYMM, 10  begin_qty, 100 in_qty, out_qty 10  From Dual Union All
Select '202307' YYYYMM, 10  begin_qty, 0   in_qty, out_qty 50  From Dual Union All
Select '202308' YYYYMM, 10  begin_qty, 50  in_qty, out_qty 0   From Dual Union All
Select '202309' YYYYMM, 10  begin_qty, 10  in_qty, out_qty 100 From Dual Union All
)

06월 전월기말 + 입고 - 출고 = 기말로 게산하고, 해당 기말을 다음 달로 이월하여 
07월 전월기말 + 입고 - 출고 = 기말을 게한 하려면 어떻게 해야 하나요?

 

by 마농 [2024.04.02 13:40:05]
WITH t_inv AS
(
SELECT '202306' yyyymm, 'A' cd, 10 begin_qty, 100 in_qty, 10 out_qty FROM dual
UNION ALL SELECT '202307', 'A', 10,  0,  50 FROM dual
UNION ALL SELECT '202308', 'A', 10, 50,   0 FROM dual
UNION ALL SELECT '202309', 'A', 10, 10, 100 FROM dual
)
SELECT yyyymm
     , cd
     , begin_qty
       - (in_qty - out_qty)
       + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm) begin_qty
     , in_qty
     , out_qty
     , begin_qty
       + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm) end_qty
  FROM t_inv
;

 


by 이정우 [2024.04.03 09:30:51]

답변 감사 합니다. 

수치가 다른 경우에는 

WITH t_inv AS

 SELECT '202301' yyyymm, 'A' cd, 10 begin_qty, 50  in_qty, 50 out_qty FROM dual UNION ALL 
 SELECT '202302' yyyymm, 'A' cd, 0  begin_qty, 50  in_qty, 50 out_qty FROM dual UNION ALL 
 SELECT '202303' yyyymm, 'A' cd, 0  begin_qty, 50 in_qty, 50 out_qty FROM dual UNION ALL
 SELECT '202304' yyyymm, 'A' cd, 10 begin_qty, 50 in_qty, 40 out_qty FROM dual UNION ALL 
 SELECT '202305' yyyymm, 'A' cd, 10 begin_qty, 50 in_qty, 50 out_qty FROM dual UNION ALL 
 SELECT '202306' yyyymm, 'A' cd, 0 begin_qty, 50 in_qty, 60 out_qty FROM dual 
)
SELECT a.*
from(  -- 기말 = 전월기말 + 입고 - 출고
    SELECT yyyymm
         , cd
         , begin_qty
           - (in_qty - out_qty)
           + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm) begin_qty
         , in_qty
         , out_qty
         , begin_qty
           + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm) end_qty
    FROM t_inv
) A 
WHERE  a.yyyymm BETWEEN  '202301'  AND '202306' ;

01월의 END(기말)이 02월의 기초(BEGIN)으로 가지 않네요. 

 

 

 


by 마농 [2024.04.03 10:48:09]

올려주신 예시에 따라 답변을 드린 것입니다.
예시를 보고 기초재고는 하나의 값이라는 가정하에 답변을 드린 것입니다.
예시가 해당 가정과 다르게 바뀌었네요.
이번 예시를 보면 기초재고가 어떻게 산정되는지? 감이 안오는데요?
기초재고에 대한 설명이 필요합니다. 기준이 어떻게 되는지?
원하는 결과물도 필요하구요.


by 이정우 [2024.04.03 14:26:31]

시작월 '202301' 의 기초재고  10 + 입고(50) - 출고(50) = 202301월 기말재고 10 

다음달 '202302' 의 기초재고 = 전달 202301의 기말재고  , 이 기초재고를 가지고 기말재고 계산 10 + 50 - 50 = 202302월의 기말재고10 이 됩니다. 

다음달 '202303' 의 기초재고 = 전달의 기말재고 = 10,  이 달의 기말재고 = 10 + 50 - 50 = 10 

이런식으로 전달의 기말재고가 다음달의 기초재고가 되고, 전달의 기말재고가 다음달 기초재고가 되어 

기초재고 + 입고 - 출고로  매월 기말재고를 재 계산해야 합니다. 

 


by 마농 [2024.04.03 14:30:55]

그렇다면? 기초재고는
1. 최초 첫번째 행에만 10 이 있고 나머지는 비어 있거나 (10, 0, 0, 0, 0, 0)
2. 그냥 하나의 값이라는 의미로 모는 행에 10 이 있거나 (10, 10, 10, 10, 10, 10)
둘 중 한가지 형태여야 할 듯 한데요?
주신 예시는 생각과 다른데? (10, 0, 0, 10, 10, 0)
왜 이런 건가요?
데이터가 일관성이 있어야 합니다.


by 이정우 [2024.04.03 14:46:15]

예, 최초 시작 할 때만 기초재고 (10)를 사용 합니다. 

다음 달 부터는 전달의 기말재고가 해당월의 기초재고가 됩니다. 즉 

다음 달부터 보여지는 기초재고는 재 계산 되어야 합니다. 

      이달의 기초재고 = 전달의 기말재고

01월의 기말재고가 다음달 02월의 기초재고가 됩니다. 

이런식으로 전달의 기말재고를 가져와 기초재고로 사용하여 기초 + 입고 - 출고 = 기말재고를 계산하고 

계산된 기말재고는 다음달 기초재고로 넘어 가면서 기말재고가 재 계산 됩니다. 

 


by 마농 [2024.04.03 15:02:48]

내용이 이해 안가는게 아니구요.
예시 자료가 이상하다는 얘기 입니다.
자료가 일관성이 없어요.


by 이정우 [2024.04.03 15:10:45]

마농님 

예시 자료에서 첫번째 행의 기초재고는 정상적인 데이터인데,

그 다음 달 부터는 잘못된 데이터라 재 계산해야 한다고 해야 할까요.

 


by 마농 [2024.04.03 15:26:49]

이상한 데이터로 예외적용을 하여 억지로 결과를 뽑을 수도 있갰지만. 추천하고 싶지 않습니다.
데이터 자체가 정상적인 데이터로 정제되어야 합니다.
지금 올려주신 데이터는 말이 안되는 자료입니다.

WITH t_inv AS
( 
SELECT '202301' yyyymm, 'A' cd, 10 begin_qty, 50 in_qty, 50 out_qty FROM dual
UNION ALL SELECT '202302', 'A',  0, 50, 50 FROM dual
UNION ALL SELECT '202303', 'A',  0, 50, 50 FROM dual
UNION ALL SELECT '202304', 'A', 10, 50, 40 FROM dual
UNION ALL SELECT '202305', 'A', 10, 50, 50 FROM dual
UNION ALL SELECT '202306', 'A',  0, 50, 60 FROM dual
)
SELECT yyyymm
     , cd
     , FIRST_VALUE(begin_qty)  OVER(PARTITION BY cd ORDER BY yyyymm)
       + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm)
       - (in_qty - out_qty) begin_qty
     , in_qty
     , out_qty
     , FIRST_VALUE(begin_qty)  OVER(PARTITION BY cd ORDER BY yyyymm)
       + SUM(in_qty - out_qty) OVER(PARTITION BY cd ORDER BY yyyymm) end_qty
  FROM t_inv
 WHERE yyyymm BETWEEN '202301' AND '202306'
;

 


by 마농 [2024.04.03 15:49:46]

지금 예를 들어 주신 자료로
1월부터 검색하면 기초재고가 10으로 시작하는데
2월부터 검색하면 기초재고가 0으로 시작하게 됩니다.
그러면 틀린 자료가 되는게 아닌가요?
혹시 기초재고라는 컬럼이 실제로 테이블에 있는 컬럼인가요?
잘못된 중간 결과물로 질문하신것은 아닌지?
중간 결과물이 아닌 원본으로 질문해주시는게 좋습니다.
원본 대비 결과표.


by 이정우 [2024.04.03 16:01:56]

감사 합니다.  

시스템 시작시 시작재고(기초)는 맞고, 입고, 출고는 맞는데,  기말재고 컬럼이 없고

기초재고 컬럼의 데이터는 타 시스템에서 데이터가 들어오는 구조인데, 잘못된 값이 들어 오고 있습니다. 

현업은 시작재고는 맞고, 입고 되고 출고 되는 재고는 맞으니까 

기말재고를 계산하여 다음달로 이월하여 다음달 기초로 사용해서 

재고수불을 볼 수 있는 레포트를 만들어 달라고 하는데, 테이블의 컬럼을 추가 할 수 도 없고, 

해당 테이블에 데이터를 업데이트 권한도 없는 입장이라 

전월달재고를 을 다음달로 이월하는 쿼리에 대해서 도움을 요청 드렸습니다. 

 

 

 


by 마농 [2024.04.03 16:15:23]

위 예시 자료에서 시작재고가 맞다는 걸 어떻게 확신하나요?
검색 시작일이 1월인 경우 시작재고가 10 이 되는 건가요? 이게 맞나요?
검색 시작일이 2월인 경우 시작재고가 0 인데? 틀런 거 아닌가요?
시작재고라는게 검색 시작일 기준이 아닌 최초 재고를 말하는 건가요?
뭔가 테이블 구조나 자료가 말이 안됩니다.

데이터가 정확하면 아무 문제 없습니다.
데이터가 부정확하면 예외처리 로직으로 보정을 해야 하는데.
그러려면 뭐가 틀리고 뭐가 맞는지 정확한 분석이 필요합니다.


by 이정우 [2024.04.03 17:43:41]

01월 재고가 기초재고는 최초재고가 맞습니다. 

그 다음달 들어오는 기초재고는 타 모듈에서 들어오는 틀린 수치라 사용이 불가

즉 2023년01월 기초재고 = 전년도에 재고실사한 재고를 업로드 = 최초재고

    입고재고  = WMS에서 정확하고 들어오는 입고수량

    출고재고  = WMS에서 정확하게 빠지는 출고수량

     02월 기초 = POS에서 트리거에 의해서 들어오는 수량 ( 틀린 데이터가 많음)

 

시스템이 변경 되면서, 2022년 12월 재고실사를 해서 2023년 01월에 기초재고에 업로드 해 놓고,

그 다음달 02월 부터는 타 모듈에서 트리거에 의해서 시작재고에 데이터가 들어오는데, 잘못된 값들이 많아서 

2023년 01월 부터 기말재고를 계산하여 다음달로 이월하여 재고를 재 계산 해 달라는 요청 입니다. 

 


by 마농 [2024.04.04 08:45:14]
WITH t_inv AS
( 
SELECT '202301' yyyymm, 'A' cd, 10 begin_qty, 50 in_qty, 50 out_qty FROM dual
UNION ALL SELECT '202302', 'A',  0, 50, 50 FROM dual
UNION ALL SELECT '202303', 'A',  0, 50, 50 FROM dual
UNION ALL SELECT '202304', 'A', 10, 50, 40 FROM dual
UNION ALL SELECT '202305', 'A', 10, 50, 50 FROM dual
UNION ALL SELECT '202306', 'A',  0, 50, 60 FROM dual
)
SELECT yyyymm
     , cd
     , begin_qty + sum_qty - (in_qty - out_qty) begin_qty
     , in_qty
     , out_qty
     , begin_qty + sum_qty end_qty
  FROM (SELECT yyyymm
             , cd
             , in_qty
             , out_qty
             , FIRST_VALUE(begin_qty) OVER(PARTITION BY cd ORDER BY yyyymm) begin_qty
             , SUM(in_qty - out_qty)  OVER(PARTITION BY cd ORDER BY yyyymm) sum_qty
          FROM t_inv
         WHERE yyyymm BETWEEN SUBSTR('202302', 1, 4) AND '202306'  -- 1월부터 검색되도록
        )
 WHERE yyyymm BETWEEN '202302' AND '202306'
;

 

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