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
Medium Priority
800 Views
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')

;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
``````
0
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
• 5
• 3

LVL 15

Expert Comment

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

ID: 35028600
where exactly is the problem ?
0

LVL 13

Author Comment

ID: 35028629
darek, it can be a batch.  -Thanks.
0

LVL 13

Author Comment

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

LVL 13

Author Comment

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

LVL 15

Expert Comment

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

``````
0

LVL 13

Author Comment

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

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
``````
0

LVL 13

Author Closing Comment

ID: 35038018
Thank you.
0

## Featured Post

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…
###### Suggested Courses
Course of the Month11 days, 14 hours left to enroll