Solved

Help with a SQL query

Posted on 2011-03-03
9
792 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 500 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

Technology Partners: 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!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 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