선입선출 조회 쿼리

fsffd 2020.10.26 11:23 Views : 783

아래와 같은 표의 데이터가 있습니다.

조회 기준은 조회년월로서 기초 + 입고 - 출고 = 기말재고의 형식인데,

이 데이터로 재고 연령조회를 해야합니다.

예를들어, 2020년 10월 기준 조회했을 때 품목명 / 1년미만 / 1년~2년 / 2년~3년 / 3년이상 의 네가지로 분류하여 조회해야되는데,

선입선출로 조회하여 각 년도별 몇개의 재고가 남았는지 확인하고싶습니다.

아래의 표를 예시로 2020년 10월로 조회했을 때

품목명 / 1년 미만 / 1년 ~ 2년 / 2년 ~ 3년 / 3년 이상

A 500 0 0 408 (총 재고의 합은 2020년 10월 기말재고)

고수님들의 조언을 듣고싶습니다.

조회년월	항목코드	기초재고	입고	출고	기말재고
201701	1010010010001	176	0	0	176
201702		176	1032	12	1196
201703		1196	0	0	1196
201704		1196	0	0	1196
201705		1196	8	16	1188
201706		1188	0	0	1188
201707		1188	40	80	1148
201708		1148	20	40	1128
201709		1128	64	64	1128
201710		1128	0	0	1128
201711		1128	40	144	1024
201712		1024	0	0	1024
201801		1024	80	80	1024
201802		1024	1536	1536	1024
201803		1024	0	0	1024
201804		1024	120	120	1024
201805		1024	0	0	1024
201806		1024	560	560	1024
201807		1024	0	0	1024
201808		1024	0	0	1024
201809		1024	0	0	1024
201810		1024	0	0	1024
201811		1024	24	24	1024
201812		1024	0	0	1024
201901		1024	0	0	1024
201902		1024	0	0	1024
201903		1024	40	40	1024
201904		1024	180	200	1004
201905		1004	0	0	1004
201906		1004	0	0	1004
201907		1004	0	0	1004
201908		1004	176	176	1004
201909		1004	0	0	1004
201910		1004	40	40	1004
201911		1004	48	48	1004
201912		1004	192	288	908
202001		908	80	80	908
202002		908	0	0	908
202003		908	80	80	908
202004		908	80	80	908
202005		908	1240	1240	908
202006		908	0	0	908
202007		908	0	0	908
202008		908	80	80	908
202009		908	80	80	908
202010		908	112	112	908

I want to

cmd_document_vote_user Update Log List

Comment 3

건우아빠

2020.10.27 10:21

10월말 재고가 908개 인데
1년미만 - 500개 / 1년~2년 - 0 / 2년~3년 - 0 / 3년이상 - 408개가 되는지를 이야기 하시는게 쉬울듯 합니다.

남아 있는 재고가 언제 들어온걸 이야기 하는 거라면 뭔가 이상 하구요 ..
선입선출이라는게 반대로 말하면 최근에 들어온게 남아 있다는 것과 일맥 상통하는거라.

네가지로 분류의 의미가 무엇인지 ?
자료를 보면 입고 출고 수량이 같은 경우가 있는데 기존재고를 나두고 입고해서 출고 시켰다는건지 ?

cmd_comment_vote_user Upvote0 Downvote0
지영아빠

2020.10.27 13:39

결과의 형태가 선입선출인데 3년이 남아 있고, 1년미만이 있다는 게 @.@
(그럴리 없을꺼 같다는 생각이라는...)
대략 구해본 쿼리입니다.

-- 주신 테이블을 seq가 들어간 테이블로 만들기

select IDENTITY(INT,1,1) AS seq, * into prd from (
select '201701' as yymm, 'A' as prd_code, 0 as in_amt,0 as out_amt, 176 as res_amt union all
select '201702' as YYMM, 'A' as prd_code, 1032 as in_amt,12 as out_amt, 1196 as res_amt union all
select '201703' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1196 as res_amt union all
select '201704' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1196 as res_amt union all
select '201705' as YYMM, 'A' as prd_code, 8 as in_amt,16 as out_amt, 1188 as res_amt union all
select '201706' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1188 as res_amt union all
select '201707' as YYMM, 'A' as prd_code, 40 as in_amt,80 as out_amt, 1148 as res_amt union all
select '201708' as YYMM, 'A' as prd_code, 20 as in_amt,40 as out_amt, 1128 as res_amt union all
select '201709' as YYMM, 'A' as prd_code, 64 as in_amt,64 as out_amt, 1128 as res_amt union all
select '201710' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1128 as res_amt union all
select '201711' as YYMM, 'A' as prd_code, 40 as in_amt,144 as out_amt, 1024 as res_amt union all
select '201712' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201801' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 1024 as res_amt union all
select '201802' as YYMM, 'A' as prd_code, 1536 as in_amt,1536 as out_amt, 1024 as res_amt union all
select '201803' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201804' as YYMM, 'A' as prd_code, 120 as in_amt,120 as out_amt, 1024 as res_amt union all
select '201805' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201806' as YYMM, 'A' as prd_code, 560 as in_amt,560 as out_amt, 1024 as res_amt union all
select '201807' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201808' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201809' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201810' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201811' as YYMM, 'A' as prd_code, 24 as in_amt,24 as out_amt, 1024 as res_amt union all
select '201812' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201901' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201902' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1024 as res_amt union all
select '201903' as YYMM, 'A' as prd_code, 40 as in_amt,40 as out_amt, 1024 as res_amt union all
select '201904' as YYMM, 'A' as prd_code, 180 as in_amt,200 as out_amt, 1004 as res_amt union all
select '201905' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1004 as res_amt union all
select '201906' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1004 as res_amt union all
select '201907' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1004 as res_amt union all
select '201908' as YYMM, 'A' as prd_code, 176 as in_amt,176 as out_amt, 1004 as res_amt union all
select '201909' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 1004 as res_amt union all
select '201910' as YYMM, 'A' as prd_code, 40 as in_amt,40 as out_amt, 1004 as res_amt union all
select '201911' as YYMM, 'A' as prd_code, 48 as in_amt,48 as out_amt, 1004 as res_amt union all
select '201912' as YYMM, 'A' as prd_code, 192 as in_amt,288 as out_amt, 908 as res_amt union all
select '202001' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 908 as res_amt union all
select '202002' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 908 as res_amt union all
select '202003' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 908 as res_amt union all
select '202004' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 908 as res_amt union all
select '202005' as YYMM, 'A' as prd_code, 1240 as in_amt,1240 as out_amt, 908 as res_amt union all
select '202006' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 908 as res_amt union all
select '202007' as YYMM, 'A' as prd_code, 0 as in_amt,0 as out_amt, 908 as res_amt union all
select '202008' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 908 as res_amt union all
select '202009' as YYMM, 'A' as prd_code, 80 as in_amt,80 as out_amt, 908 as res_amt union all
select '202010' as YYMM, 'A' as prd_code, 112 as in_amt,112 as out_amt, 908 as res_amt
) x

-- dummy용 테이블

with dummy as
(
select 1 Number
union all
select Number +1 from dummy where Number<10000
)
select * into dum from dummy
option (maxrecursion 0)

실제 쿼리

with tmp as (
select
seq,
yymm,
prd_code,
(case when seq = 1 and in_amt =0 and res_amt > 0 then res_amt else in_amt end) as in_amt,
out_amt
from
prd
)
select
in_status.yymm, in_status.prd_code, count(*) as cnt
from (
-- 입고
select a.yymm, a.prd_code, b.number
from (
select seq, yymm, prd_code, out_amt,
case when seq = 1 then 1 else (select sum(in_amt) + 1 from tmp where seq <= x.seq -1 ) end as start_num,
case when seq = 1 then in_amt else (select sum(in_amt) from tmp where seq <= x.seq -1 ) + in_amt end as end_num
from tmp x
where in_amt > 0
) a
inner join dum b
on b.number between a.start_num and a.end_num
) in_status
left outer join (
-- 출고
select
c.yymm, c.prd_code, d.number
from (
select
seq, yymm, prd_code, out_amt,
case when seq = 1 then 1 else (select sum(out_amt) + 1 from tmp where seq <= x.seq -1 ) end as start_num,
case when seq = 1 then out_amt else (select sum(out_amt) from tmp where seq <= x.seq -1 ) + out_amt end as end_num
from tmp x where out_amt > 0
) c
inner join dum d
on d.number between c.start_num and c.end_num
) out_status
on in_status.number = out_status.number
where
out_status.number is null
group by
in_status.yymm, in_status.prd_code
order by 1

-- 결과는 대략 이런 식

결국 908개가 남긴 하네요

cmd_comment_vote_user Upvote0 Downvote0
지영아빠

2020.11.02 16:09

select
x.seq,
x.yymm,
x.prd_code,
x.in_amt ,
case when x.seq = y.seq then z.res_amt - (select sum(in_amt) from prd where seq > x.seq)
else x.in_amt end as res
from
prd x
inner join (select
max(seq) as seq
from
prd x
cross apply (select sum(in_amt) as sum_amt from prd where seq >= x.seq +1) y
where in_amt > 0
and in_amt + isnull(sum_amt, 0) > (select top 1 res_amt from prd order by seq desc)
) y
cross join (select top 1 res_amt from prd order by seq desc) z
on
x.seq >= y.seq
where x.in_amt > 0
order by x.seq

이런 식으로도 구할 수 있을 것 같네요 ...

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36501
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	19832
10663	sql 쿼리 관련 질문 드립니다. [3]	코딩왕김탁구	2020.11.15	426
10662	일반 문자열을 utf-8 문자열로 변환 함수가 있을까요? [1]	강구다..	2020.11.13	525
10661	SQL 복합 정렬 문제 도움좀 부탁드립니다. [1]	아들바보	2020.11.10	392
10660	WRITELOG 관련하여 문의 드립니다. [5]	런아웃	2020.11.09	518
10659	특정 테이블 백업 방법 [1]	푸른산	2020.11.05	852
10658	XQuery 사용시 질문 사항 [2]	날렵한곰	2020.11.03	592
10657	[MSSQL]LIKE 절 관해 질문드립니다.	carlospark	2020.10.29	473
10656	쿼리문 하나 질문합니다. [2]	그린슈퍼	2020.10.28	430
»	선입선출 조회 쿼리 [3]	fsffd	2020.10.26	783
10654	sql2008 -> sql2019로 restore 시킨후 호환성(150)에서만 기존 프로시저 에러 발생 이유? [4]	건우아빠	2020.10.23	504
10653	Oracle 행을 열로 변환하는방법... 질문드립니다	김길동	2020.10.22	626
10652	SQL Server 머신러닝 R 쿼리 관련 질문드려요!! ㅠㅠ [2]	Mikha	2020.10.21	497
10651	CONVERT 에러 _ 도와주십쇼 [2]	아침고기	2020.10.20	2148
10650	[MSSQL] 대량 테이블 조인 - HINT 문의	아침고기	2020.10.20	466
10649	CONVERT 에러 _ 도와주십쇼	아침고기	2020.10.19	389
10648	SQL 고수님들 답변 부탁드립니다.. [1]	kimmitator	2020.10.19	384
10647	There is insufficient memory available in the buffer pool. 관련입니다.	NumNull	2020.10.15	396
10646	MDF, LDF 용량 관리에 대한 질문입니다. [1]	울어욥	2020.10.08	1007
10645	select 절내의 집계쿼리 질문 입니다. [2]	양아~	2020.09.21	562
10644	view 생성문 문제입니다.	청소부	2020.09.21	489

Write Tag

First Page 11 12 13 14 15 16 17 18 19 20 Last Page

선입선출 조회 쿼리

Comment 3

건우아빠

지영아빠

지영아빠

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발