Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with a SQL query

Posted on 2011-03-03
9
Medium Priority
?
800 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35028575
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
ID: 35028600
where exactly is the problem ?
0
 
LVL 13

Author Comment

by:Rick
ID: 35028629
darek, it can be a batch.  -Thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Author Comment

by:Rick
ID: 35028671
ashilo, my query is not returning the expected result.  -Thanks.
0
 
LVL 13

Author Comment

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

Expert Comment

by:derekkromm
ID: 35028821
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
ID: 35030238
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 2000 total points
ID: 35030297
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
ID: 35038018
Thank you.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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