Solved

Help with SQL query

Posted on 2011-03-01
10
265 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
  • 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
 
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 40

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

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 40

Accepted Solution

by:
Sharath earned 500 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 40

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now