Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 803
  • Last Modified:

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



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
Rick
Asked:
Rick
  • 5
  • 3
1 Solution
 
derekkrommCommented:
Can this be a batch (like a stored proc), or does it need to be a single SQL statement?
0
 
Aaron ShiloChief Database ArchitectCommented:
where exactly is the problem ?
0
 
RickAuthor Commented:
darek, it can be a batch.  -Thanks.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
RickAuthor Commented:
ashilo, my query is not returning the expected result.  -Thanks.
0
 
RickAuthor Commented:
... and I don't know where the problem is. -Thanks.
0
 
derekkrommCommented:
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
 
RickAuthor Commented:
Well, I don't know what I'm doing wrong...
Could you post the rest of your solution?

-Thanks.
0
 
derekkrommCommented:
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
 
RickAuthor Commented:
Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now