Solved

Help with a SQL query

Posted on 2011-03-03
9
773 Views
Last Modified: 2012-05-11
Hello,

Please look at the query below.

#tbl1 holds a list of SKUs and Quantities.
#tbl2 is the 'raw data' table. It holds a list of all SKUs sold, the Qty sold and the Location the SKU was sold to.

I need to take the Qty from #tbl1for each SKU and find out how many units were sold in these locations: US, JP, EU, and SA, based on LIFO.

So, for example:

SKU 'ABC' has a Qty of 84 in #tbl1, based on LIFO we can determine that all 84 pieces sold in JP:

ABC      2010-12-26 00:00:00.000      24      JP          (24 units from 12/26/2010)
ABC      2010-12-16 00:00:00.000      1      JP          (1 unit from 12/16/2010)
ABC      2010-12-10 13:28:54.000      56      JP          (56 units from 12/04/2010)
ABC      2010-12-04 00:00:00.000      1      JP          (1 unit from 12/04/2010)
ABC      2010-11-30 05:13:30.000      40      JP          (and we need only 2 units from 11/30/2010 to make 84 tota)


SKU 'JKL' has a Qty of 320 in #tbl1, based on LIFO we can determine that 40 pieces sold in the US and the remaining 280 sold in EU:

JKL      2010-12-26 00:00:00.000      40      US          (40 units sold in the US on 12/26/2010)
JKL      2010-12-13 10:42:24.000      330      EU          (and if we take 280 units sold in EU on 12/13/2010, it makes a total
                                                                              of 320 units
)


This is the expected result:

SKU        US        JP        SA        EU
------------------------------------------
ABC         0         84         0          0
DEF          0          0        74          0
GHI          0          0         15          0
JKL        40          0          0       280
MNO        0        15          0          0



if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2


create table #tbl1
(
SKU char(3),
Qty int
)


create table #tbl2
(
SKU char(3),
SELDATE datetime,
Qty int,
LOC char(2)
)

insert into #tbl1 values('ABC', 84)
insert into #tbl1 values('DEF', 74)
insert into #tbl1 values('GHI', 15)
insert into #tbl1 values('JKL', 320)
insert into #tbl1 values('MNO', 15)


insert into #tbl2 values('GHI', '2009-06-30 08:23:02.000', 20, 'SA')
insert into #tbl2 values('MNO', '2010-12-17 00:00:00.000', 4, 'JP')
insert into #tbl2 values('DEF', '2010-01-26 08:33:19.000', 84, 'SA')
insert into #tbl2 values('DEF', '2009-10-16 10:32:32.000', 16, 'SA')
insert into #tbl2 values('ABC', '2010-12-26 00:00:00.000', 24, 'JP')
insert into #tbl2 values('ABC', '2010-12-04 00:00:00.000', 1, 'JP')
insert into #tbl2 values('ABC', '2010-12-16 00:00:00.000', 1, 'JP')
insert into #tbl2 values('MNO', '2010-11-30 05:13:30.000', 12, 'JP')
insert into #tbl2 values('ABC', '2010-11-30 05:13:30.000', 40, 'JP')
insert into #tbl2 values('ABC', '2010-12-10 13:28:54.000', 56, 'JP')
insert into #tbl2 values('JKL', '2010-12-13 10:42:24.000', 330, 'EU')
insert into #tbl2 values('JKL', '2010-12-26 00:00:00.000', 40, 'US')


---- Please help fix this:
;with cte as (
select *,ROW_NUMBER() over (partition by SKU,LOC order by SELDATE desc) rn
  from  #tbl2),
cte2 as (select * from cte where rn = 1),
cte3 as (select *,ROW_NUMBER() over (partition by SKU order by Qty) rn2 from cte2),
cte4 as (
select *,(select SUM(Qty) from cte3 c2 where c1.SKU = c2.SKU and c2.rn2<=c1.rn2) Run_Qty
  from cte3 c1),
cte5 as (
select c1.SKU,c1.LOC,c1.SELDATE,c1.Qty + case when c1.Run_Qty > t1.Qty then t1.Qty-c1.Run_Qty else 0 end Final_Qty
  from cte4 c1
  join #tbl1 t1 on c1.SKU = t1.SKU)
select SKU,
		isnull([US], 0) [US],
		isnull([JP], 0) [JP],
		isnull([SA], 0) [SA],
		isnull([EU], 0) [EU]
  from (
select SKU,LOC,case when Final_Qty < 0 then 0 else Final_Qty end Final_Qty
  from cte5) t1
pivot (max(Final_Qty) for LOC in (
		[US],
		[JP],
		[SA],
		[EU]
)) as p

Open in new window

0
Comment
Question by:Rick
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
Can this be a batch (like a stored proc), or does it need to be a single SQL statement?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
where exactly is the problem ?
0
 
LVL 13

Author Comment

by:Rick
Comment Utility
darek, it can be a batch.  -Thanks.
0
 
LVL 13

Author Comment

by:Rick
Comment Utility
ashilo, my query is not returning the expected result.  -Thanks.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 13

Author Comment

by:Rick
Comment Utility
... and I don't know where the problem is. -Thanks.
0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
Is this enough to get you started? It should get you the required source data (see the QtyUsed column), you just need to aggregate it by SKU/LOC and add in the pivot.
if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2


create table #tbl1
(
SKU char(3),
Qty int
)


create table #tbl2
(
SKU char(3),
SELDATE datetime,
Qty int,
LOC char(2)
)

insert into #tbl1 values('ABC', 84)
insert into #tbl1 values('DEF', 74)
insert into #tbl1 values('GHI', 15)
insert into #tbl1 values('JKL', 320)
insert into #tbl1 values('MNO', 15)


insert into #tbl2 values('GHI', '2009-06-30 08:23:02.000', 20, 'SA')
insert into #tbl2 values('MNO', '2010-12-17 00:00:00.000', 4, 'JP')
insert into #tbl2 values('DEF', '2010-01-26 08:33:19.000', 84, 'SA')
insert into #tbl2 values('DEF', '2009-10-16 10:32:32.000', 16, 'SA')
insert into #tbl2 values('ABC', '2010-12-26 00:00:00.000', 24, 'JP')
insert into #tbl2 values('ABC', '2010-12-04 00:00:00.000', 1, 'JP')
insert into #tbl2 values('ABC', '2010-12-16 00:00:00.000', 1, 'JP')
insert into #tbl2 values('MNO', '2010-11-30 05:13:30.000', 12, 'JP')
insert into #tbl2 values('ABC', '2010-11-30 05:13:30.000', 40, 'JP')
insert into #tbl2 values('ABC', '2010-12-10 13:28:54.000', 56, 'JP')
insert into #tbl2 values('JKL', '2010-12-13 10:42:24.000', 330, 'EU')
insert into #tbl2 values('JKL', '2010-12-26 00:00:00.000', 40, 'US')

select * from #tbl1;

with cte as (
	select *, ROW_NUMBER() over (partition by SKU order by SELDATE desc) rn from #tbl2 
	)
	

select 
	t2.*, 
	t1.Qty TotalQty, 
	case 
		when t2.RunningTotal <= t1.Qty then t2.Qty
		when t2.RunningTotal - t2.Qty < t1.Qty then t1.Qty - (t2.RunningTotal - t2.Qty)
		else 0
	end QtyUsed
from
	#tbl1 t1
	inner join (
		select 
			c.SKU, c.SELDATE, c.LOC, c.Qty, c.rn,  SUM(c1.Qty) RunningTotal
		from 
			cte c
			inner join cte c1
				on	c.SKU = c1.SKU and c1.rn <= c.rn
		group by
			c.SKU, c.SELDATE, c.LOC, c.Qty, c.rn) t2
		on	t1.SKU = t2.SKU
order by
	t2.SKU, t2.rn
	

Open in new window

0
 
LVL 13

Author Comment

by:Rick
Comment Utility
Well, I don't know what I'm doing wrong...
Could you post the rest of your solution?

-Thanks.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
Comment Utility
if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2


create table #tbl1
(
SKU char(3),
Qty int
)


create table #tbl2
(
SKU char(3),
SELDATE datetime,
Qty int,
LOC char(2)
)

insert into #tbl1 values('ABC', 84)
insert into #tbl1 values('DEF', 74)
insert into #tbl1 values('GHI', 15)
insert into #tbl1 values('JKL', 320)
insert into #tbl1 values('MNO', 15)


insert into #tbl2 values('GHI', '2009-06-30 08:23:02.000', 20, 'SA')
insert into #tbl2 values('MNO', '2010-12-17 00:00:00.000', 4, 'JP')
insert into #tbl2 values('DEF', '2010-01-26 08:33:19.000', 84, 'SA')
insert into #tbl2 values('DEF', '2009-10-16 10:32:32.000', 16, 'SA')
insert into #tbl2 values('ABC', '2010-12-26 00:00:00.000', 24, 'JP')
insert into #tbl2 values('ABC', '2010-12-04 00:00:00.000', 1, 'JP')
insert into #tbl2 values('ABC', '2010-12-16 00:00:00.000', 1, 'JP')
insert into #tbl2 values('MNO', '2010-11-30 05:13:30.000', 12, 'JP')
insert into #tbl2 values('ABC', '2010-11-30 05:13:30.000', 40, 'JP')
insert into #tbl2 values('ABC', '2010-12-10 13:28:54.000', 56, 'JP')
insert into #tbl2 values('JKL', '2010-12-13 10:42:24.000', 330, 'EU')
insert into #tbl2 values('JKL', '2010-12-26 00:00:00.000', 40, 'US')

select * from #tbl1;

with cte as (
	select *, ROW_NUMBER() over (partition by SKU order by SELDATE desc) rn from #tbl2 
	)
	

select SKU,
		isnull([US], 0) [US],
		isnull([JP], 0) [JP],
		isnull([SA], 0) [SA],
		isnull([EU], 0) [EU]
  from (
	select SKU, LOC, sum(QtyUsed) QtyUsed from (
		select 
			t2.*, 
			t1.Qty TotalQty, 
			case 
				when t2.RunningTotal <= t1.Qty then t2.Qty
				when t2.RunningTotal - t2.Qty < t1.Qty then t1.Qty - (t2.RunningTotal - t2.Qty)
				else 0
			end QtyUsed
		from
			#tbl1 t1
			inner join (
				select 
					c.SKU, c.SELDATE, c.LOC, c.Qty, c.rn,  SUM(c1.Qty) RunningTotal
				from 
					cte c
					inner join cte c1
						on	c.SKU = c1.SKU and c1.rn <= c.rn
				group by
					c.SKU, c.SELDATE, c.LOC, c.Qty, c.rn) t2
				on	t1.SKU = t2.SKU
			) a
			group by SKU, LOC
		) t 
	pivot (sum(QtyUsed) for LOC in ([US], [JP], [SA], [EU])) as p

Open in new window

0
 
LVL 13

Author Closing Comment

by:Rick
Comment Utility
Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now