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

x
?
Solved

Help with SQL query

Posted on 2011-03-01
10
Medium Priority
?
293 Views
Last Modified: 2012-06-21
Related open question : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26853176.html



In SQL 2005, Comp level 80, I have two tables.

tbl1 is a "summary table" which holds information about SKU Quantities (SKU, Qty, and Qty sold in each region).

SKU    QTY       US     JP       EU
------------------------------------------------
ABC  	5	NULL	NULL	NULL
DEF	3	NULL	NULL	NULL
GHI	10	NULL	NULL	NULL


tbl2 is the "raw data" table which holds information about each SKU sold (SKU, Qty sold, Region it was sold to, and Date sold).

SKU   Qty      Region     Date Sold
------------------------------------------------
ABC	3	JP	2011-01-11 00:00:00
ABC	2	US	2011-01-10 00:00:00
DEF	1	JP	2011-01-10 00:00:00
DEF	1	EU	2011-01-10 00:00:00
DEF	5	US	2011-01-09 00:00:00
GHI	3	US	2011-01-09 00:00:00
GHI	4	EU	2011-01-08 00:00:00
GHI	7	JP	2011-01-07 00:00:00
ABC	13	JP	2010-01-11 00:00:00
ABC	12	US	2010-01-10 00:00:00
DEF	11	JP	2010-01-10 00:00:00
DEF	11	EU	2010-01-10 00:00:00
GHI	13	US	2010-01-09 00:00:00
DEF	15	US	2010-01-09 00:00:00
GHI	14	EU	2010-01-08 00:00:00
GHI	17	JP	2010-01-07 00:00:00


I need to update the regions on tbl1 based on most recent sold items.
This is the expected result:


SKU    Qty        US       JP           EU
--------------------------------------------
ABC     5          2        3            0
DEF     3          1        1            1
GHI    10          3        4            3


Thank you!

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
  • 4
10 Comments
 
LVL 13

Author Comment

by:Rick
ID: 35009041

if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2

create table #tbl2
(
SKU char(3),
Qty int,
DC char(2),
OprDate smalldatetime
)

create table #tbl1
(
SKU char(3),
Qty int,
US char(2),
JP char(2),
EU char(2)
)


insert into #tbl2 values('ABC',2,'US','2011-01-10')
insert into #tbl2 values('DEF',1,'JP','2011-01-10')
insert into #tbl2 values('DEF',5,'US','2011-01-09')
insert into #tbl2 values('GHI',4,'EU','2011-01-08')
insert into #tbl2 values('GHI',7,'JP','2011-01-07')
insert into #tbl2 values('ABC',3,'JP','2011-01-11')
insert into #tbl2 values('GHI',3,'US','2011-01-09')
insert into #tbl2 values('DEF',1,'EU','2011-01-10')
insert into #tbl2 values('ABC',12,'US','2010-01-10')
insert into #tbl2 values('DEF',11,'JP','2010-01-10')
insert into #tbl2 values('DEF',15,'US','2010-01-09')
insert into #tbl2 values('GHI',14,'EU','2010-01-08')
insert into #tbl2 values('GHI',17,'JP','2010-01-07')
insert into #tbl2 values('ABC',13,'JP','2010-01-11')
insert into #tbl2 values('GHI',13,'US','2010-01-09')
insert into #tbl2 values('DEF',11,'EU','2010-01-10')


INSERT INTO #tbl1 (SKU, Qty) VALUES('ABC', 5)
INSERT INTO #tbl1 (SKU, Qty) VALUES('DEF', 3)
INSERT INTO #tbl1 (SKU, Qty) VALUES('GHI', 10)


/*   :: Not Working ::

UPDATE #tbl1
   SET 
       US = source.US,
       JP = source.JP,
       EU = source.EU
  FROM #tbl1
       INNER JOIN (select pvt.SKU, SUM(pvt.US) US, SUM(pvt.JP) JP, SUM(pvt.EU) EU from (       
				   select SKU, DC, OprDate, Qty [US], 0 [JP], 0 [EU] from #tbl2 where DC = 'US' UNION
				   select SKU, DC, OprDate, 0 [US], Qty [JP], 0 [EU] from #tbl2 where DC = 'JP' UNION
				   select SKU, DC, OprDate, 0 [US], 0 [JP], Qty [EU] from #tbl2 where DC = 'EU') pvt,
				   (select SKU, DC, MAX(OprDate) as MaxOprDate from #tbl2 group by SKU, DC) pmax
				   where pvt.SKU = pmax.SKU and pvt.DC = pmax.DC and pvt.OprDate = pmax.MaxOprDate
				   group by pvt.SKU
				   ) as Source
       ON #tbl1.SKU = Source.SKU
*/		

select * from #tbl1
select * from #tbl2 order by OprDate Desc, DC Desc

Open in new window

0
 
LVL 13

Author Comment

by:Rick
ID: 35009075
Please note that I don't want to update the Qty field in tbl1, and also that the sum of US, JP, EU is equal to Qty.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35009376
I tried to extract the most recent Qty value for each combiniation of SKU and DC as follows:
 SELECT SKU, Qty, DC
 FROM
 (
  Select SKU, DC, Qty, OprDate
  , RN = ROW_NUMBER () OVER (PARTITION BY SKU, DC ORDER BY OprDate Desc)
  FROM #tbl2
 ) ilv
 where RN = 1

However, this produces different results to your sample results.

Using the above in a pivot table query:

SELECT SKU,  "US", "JP", "EU"
FROM
(
 SELECT SKU, Qty, DC
 FROM
 (
  Select SKU, DC, Qty, OprDate
  , RN = ROW_NUMBER () OVER (PARTITION BY SKU, DC ORDER BY OprDate Desc)
  FROM #tbl2
 ) ilv
 where RN = 1

) p
PIVOT
(
  SUM(Qty)
  FOR DC in ("US","JP","EU")
) pvt

gives
SKU      US      JP      EU
ABC      2      3      NULL
DEF      5      1      1
GHI      3      7      4

Could you check your sample results and let me know if your or mine results are correct?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Author Comment

by:Rick
ID: 35010203
Hi Paul,

I ran your query and it seems like SKU ABC is right.
The query took the top 5 units (3 units from JP and 2 from US)

ABC      3      JP      2011-01-11 00:00:00
ABC      2      US      2011-01-10 00:00:00


The other two are not right.
For DEF, I wanted the top 3 units (1 JP, 1 EU, and 1 US):

DEF      1      JP      2011-01-10 00:00:00
DEF      1      EU      2011-01-10 00:00:00
DEF      5      US      2011-01-09 00:00:00

... but it gave me all 7 units instead.


And for GHI, I wanted the top 10 units (3 US, 4 EU, 3 JP):

GHI      3      US      2011-01-09 00:00:00
GHI      4      EU      2011-01-08 00:00:00
GHI      7      JP      2011-01-07 00:00:00

... but it gave me all 14 units instead.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35011795
The most recent Qty for GHI and JP is 7. What is the logic behind getting this value as 4 in your expected result?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35011935
BTW, why did you open another question when you already started a thread on this?
0
 
LVL 13

Author Comment

by:Rick
ID: 35012186
Sharath,

I meant to say 3 from JP, not 4.

As stated on my other post, I need to take n units, from the Qty column and distribute them between US, JP, and EU. In other words, 5 ABCs, 3 DEFs and 10 GHIs, and no more than that.

Because I'm using LIFO, I can only use 3 units from JP for GHI.

SKU    Qty        US       JP        EU
--------------------------------------------
ABC     5          2        3            0
DEF      3          1        1            1
GHI    10          3        3            4


I don't necessarily need to update tbl1... would be good if it could be done, but if not, the results can simply be displayed in query analyzer or on a 3rd table (#tbl3, for example).

...

I started another thread in hope that someone else would help to solve this.

Thanks.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35012288
Try this query.
;with cte as (
select *,ROW_NUMBER() over (partition by SKU,DC order by OprDate 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.DC,c1.OprDate,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 *
  from (
select SKU,DC,case when Final_Qty < 0 then 0 else Final_Qty end Final_Qty
  from cte5) t1
 pivot (max(Final_Qty) for DC in ([US],[JP],[EU])) as p

Open in new window

Tested with your sample.
if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2

create table #tbl2
(
SKU char(3),
Qty int,
DC char(2),
OprDate smalldatetime
)

create table #tbl1
(
SKU char(3),
Qty int,
US char(2),
JP char(2),
EU char(2)
)


insert into #tbl2 values('ABC',2,'US','2011-01-10')
insert into #tbl2 values('DEF',1,'JP','2011-01-10')
insert into #tbl2 values('DEF',5,'US','2011-01-09')
insert into #tbl2 values('GHI',4,'EU','2011-01-08')
insert into #tbl2 values('GHI',7,'JP','2011-01-07')
insert into #tbl2 values('ABC',3,'JP','2011-01-11')
insert into #tbl2 values('GHI',3,'US','2011-01-09')
insert into #tbl2 values('DEF',1,'EU','2011-01-10')
insert into #tbl2 values('ABC',12,'US','2010-01-10')
insert into #tbl2 values('DEF',11,'JP','2010-01-10')
insert into #tbl2 values('DEF',15,'US','2010-01-09')
insert into #tbl2 values('GHI',14,'EU','2010-01-08')
insert into #tbl2 values('GHI',17,'JP','2010-01-07')
insert into #tbl2 values('ABC',13,'JP','2010-01-11')
insert into #tbl2 values('GHI',13,'US','2010-01-09')
insert into #tbl2 values('DEF',11,'EU','2010-01-10')

INSERT INTO #tbl1 (SKU, Qty) VALUES('ABC', 5)
INSERT INTO #tbl1 (SKU, Qty) VALUES('DEF', 3)
INSERT INTO #tbl1 (SKU, Qty) VALUES('GHI', 10)

;with cte as (
select *,ROW_NUMBER() over (partition by SKU,DC order by OprDate 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.DC,c1.OprDate,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 *
  from (
select SKU,DC,case when Final_Qty < 0 then 0 else Final_Qty end Final_Qty
  from cte5) t1
 pivot (max(Final_Qty) for DC in ([US],[JP],[EU])) as p
/*
SKU	US	JP	EU
ABC	2	3	NULL
DEF	1	1	1
GHI	3	3	4
*/

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35012306
To display 0 instead of NULL.
if OBJECT_ID('tempdb..#tbl1') is not null drop table #tbl1
if OBJECT_ID('tempdb..#tbl2') is not null drop table #tbl2

create table #tbl2
(
SKU char(3),
Qty int,
DC char(2),
OprDate smalldatetime
)

create table #tbl1
(
SKU char(3),
Qty int,
US char(2),
JP char(2),
EU char(2)
)


insert into #tbl2 values('ABC',2,'US','2011-01-10')
insert into #tbl2 values('DEF',1,'JP','2011-01-10')
insert into #tbl2 values('DEF',5,'US','2011-01-09')
insert into #tbl2 values('GHI',4,'EU','2011-01-08')
insert into #tbl2 values('GHI',7,'JP','2011-01-07')
insert into #tbl2 values('ABC',3,'JP','2011-01-11')
insert into #tbl2 values('GHI',3,'US','2011-01-09')
insert into #tbl2 values('DEF',1,'EU','2011-01-10')
insert into #tbl2 values('ABC',12,'US','2010-01-10')
insert into #tbl2 values('DEF',11,'JP','2010-01-10')
insert into #tbl2 values('DEF',15,'US','2010-01-09')
insert into #tbl2 values('GHI',14,'EU','2010-01-08')
insert into #tbl2 values('GHI',17,'JP','2010-01-07')
insert into #tbl2 values('ABC',13,'JP','2010-01-11')
insert into #tbl2 values('GHI',13,'US','2010-01-09')
insert into #tbl2 values('DEF',11,'EU','2010-01-10')

INSERT INTO #tbl1 (SKU, Qty) VALUES('ABC', 5)
INSERT INTO #tbl1 (SKU, Qty) VALUES('DEF', 3)
INSERT INTO #tbl1 (SKU, Qty) VALUES('GHI', 10)

;with cte as (
select *,ROW_NUMBER() over (partition by SKU,DC order by OprDate 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.DC,c1.OprDate,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(EU,0) EU
  from (
select SKU,DC,case when Final_Qty < 0 then 0 else Final_Qty end Final_Qty
  from cte5) t1
 pivot (max(Final_Qty) for DC in ([US],[JP],[EU])) as p
/*
SKU	US	JP	EU
ABC	2	3	0
DEF	1	1	1
GHI	3	3	4
*/

Open in new window

0
 
LVL 13

Author Closing Comment

by:Rick
ID: 35012317
Very good!  -Thank you.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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