Rick
asked on
Help with a SQL query
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
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
derekkromm
Can this be a batch (like a stored proc), or does it need to be a single SQL statement?
where exactly is the problem ?
ASKER
darek, it can be a batch. -Thanks.
ASKER
ashilo, my query is not returning the expected result. -Thanks.
ASKER
... and I don't know where the problem is. -Thanks.
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
ASKER
Well, I don't know what I'm doing wrong...
Could you post the rest of your solution?
-Thanks.
Could you post the rest of your solution?
-Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.