[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Help with SQL query

Posted on 2011-02-28
Medium Priority
352 Views
Hi,

I have the following two tables:

tbl1
---------------------------------
SKU     Qty   US     JP      EU

ABC       5
DEF        3
GHI         10

tbl2
------------------------------------------
SKU       Qty      DC         OprDate
ABC       2          US       2011-01-10
DEF        1          JP        2011-01-10
DEF        5          US       2011-01-09
GHI         4          EU       2011-01-08
GHI         7         JP         2011-01-07
ABC       3          JP        2011-01-11
GHI         3          US       2011-01-09
DEF        1          EU       2011-01-10

I  need to update tbl1 based on most recent records from tbl2 (as if tbl2 was ordered by OprDate Desc, DC Desc).

The sum of US, JP and EU must be equal to Qty.
So, 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!
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
• 10
• 5
• 4
• +1

LVL 25

Expert Comment

ID: 34999602
Something like this -
``````update t1 set US = (select sum(t2.qty) from tbl2 t2 where t2.sku = t1.sku and t2.DC = 'US') from tbl1;
update t1 set JP = (select sum(t2.qty) from tbl2 t2 where t2.sku = t1.sku and t2.DC = 'JP') from tbl1;
update t1 set EU = (select sum(t2.qty) from tbl2 t2 where t2.sku = t1.sku and t2.DC = 'EU') from tbl1;
``````
0

LVL 50

Expert Comment

ID: 34999939
this selects the data
``````drop table #tbl2
drop table #tbl1
select
'ABC' as sku,       5 as qty,nullif(0,0) as us,nullif(0,0) as jp, nullif(0,0) as eu
into #tbl1
Select
'ABC' as sku,       2 as qty,'US' as dc,'2011-01-10' as oprdate
into #tbl2

Select a.sku,a.qty,b.us,b.jp,b.eu
,case when a.qty <> coalesce(b.us,0)
+coalesce(b.jp,0)+coalesce(b.eu,0)
then 'Mismatch'
else ''
end as checktotals
from #tbl1 as a
left outer join
(select sku,[US],[JP],[EU]
from
(select *
from
(select sku,qty,dc
,ROW_NUMBER() over (partition by sku,dc order by oprdate desc) as rn
from #tbl2
where dc in ('US','JP','EU')
) as x
where rn=1
) as y
pivot (max(qty) for dc in ([US],[JP],[EU])) as pvt
) as B
on a.sku=b.sku
order by 1
``````
0

LVL 50

Expert Comment

ID: 34999941
this updates if the totals match
``````Update #tbl1
set us=x.us,jp=x.jp,eu=b.eu
from #tbl1
Inner join (
Select a.sku,a.qty,b.us,b.jp,b.eu
,case when a.qty <> coalesce(b.us,0)
+coalesce(b.jp,0)+coalesce(b.eu,0)
then 'Mismatch'
else ''
end as checktotals
from #tbl1 as a
left outer join
(select sku,[US],[JP],[EU]
from
(select *
from
(select sku,qty,dc
,ROW_NUMBER() over (partition by sku,dc order by oprdate desc) as rn
from #tbl2
where dc in ('US','JP','EU')
) as x
where rn=1
) as y
pivot (max(qty) for dc in ([US],[JP],[EU])) as pvt
) as B
on a.sku=b.sku
) as X
on #tbl1.sku=x.sku
and x.checktotals=''
``````
0

LVL 23

Expert Comment

ID: 35000280

``````MERGE tbl1 as Target
USING (select SKU, ISNULL([US],0) US, ISNULL([JP],0) JP, ISNULL([EU],0) EU
from (select t1.SKU, t1.Qty, t1.DC
from tbl2 t1
inner join (select SKU, DC, MAX(OprDate) as MaxOprDate from tbl2 group by SKU, DC) t2
on t1.SKU = t2.SKU and t1.DC = t2.DC and t1.OprDate = t2.MaxOprDate) p
PIVOT (SUM(Qty) FOR DC IN (US, JP, EU)) pvt) as Source
ON (target.SKU = source.SKU)
WHEN MATCHED THEN
UPDATE SET Qty = source.US+source.JP+source.EU,
US = source.US,
JP = source.JP,
EU = source.EU
WHEN NOT MATCHED THEN
INSERT (SKU, Qty, US, JP, EU) VALUES (source.SKU, source.US+source.JP+source.EU, source.US, source.JP, source.EU);
``````

Here is the unit test:

``````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',2,'US','2010-01-10')
insert into #tbl2 values('DEF',1,'JP','2010-01-10')
insert into #tbl2 values('DEF',5,'US','2010-01-09')
insert into #tbl2 values('GHI',4,'EU','2010-01-08')
insert into #tbl2 values('GHI',7,'JP','2010-01-07')
insert into #tbl2 values('ABC',3,'JP','2010-01-11')
insert into #tbl2 values('GHI',3,'US','2010-01-09')
insert into #tbl2 values('DEF',1,'EU','2010-01-10')

MERGE #tbl1 as Target
USING (select SKU, ISNULL([US],0) US, ISNULL([JP],0) JP, ISNULL([EU],0) EU
from (select t1.SKU, t1.Qty, t1.DC
from #tbl2 t1
inner join (select SKU, DC, MAX(OprDate) as MaxOprDate from #tbl2 group by SKU, DC) t2
on t1.SKU = t2.SKU and t1.DC = t2.DC and t1.OprDate = t2.MaxOprDate) p
PIVOT (SUM(Qty) FOR DC IN (US, JP, EU)) pvt) as Source
ON (target.SKU = source.SKU)
WHEN MATCHED THEN
UPDATE SET Qty = source.US+source.JP+source.EU,
US = source.US,
JP = source.JP,
EU = source.EU
WHEN NOT MATCHED THEN
INSERT (SKU, Qty, US, JP, EU) VALUES (source.SKU, source.US+source.JP+source.EU, source.US, source.JP, source.EU);

select * from #tbl1
``````
0

LVL 13

Author Comment

ID: 35000934
wdosanjos,

I'm using SQL 2005 and it gave me this error message : "Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel."

0

LVL 13

Author Comment

ID: 35000960

Same thing :)

0

LVL 50

Expert Comment

ID: 35001381
what compatability level are you running the database at?

the old method
``````Update #tbl1
set us=x.us,jp=x.jp,eu=b.eu
from #tbl1
Inner join (
Select a.sku,b.us,b.jp,b.eu
,case when a.qty <> coalesce(b.us,0)
+coalesce(b.jp,0)+coalesce(b.eu,0)
then 'Mismatch'
else ''
end as checktotals
from #tbl1 as a
left outer join
(select sku,
,MAX(case dc when 'US' then qty end) as US
,MAX(case dc when 'JP' then qty end) as JP
,MAX(case dc when 'EU' then qty end) as EU
from #tbl2 as z
where dc in ('US','JP','EU')
and not exists (select sku from #tbl2  as x
where x.sku=z.sku
and x.oprdate > z.oprdata
and x.dc=z.dc)
group by sku
) as x
on a.sku=x.sku
) as x
on #tbl1.sku=x.sku
and x.checktotals=''
``````
0

LVL 13

Author Comment

ID: 35001862

The compatibility level is 80.
Your last post gave me 2 error messages:

"Msg 102, Level 15, State 1, Line 14. Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 23. Incorrect syntax near the keyword 'group'.

0

LVL 23

Expert Comment

ID: 35002211
Here is the revised script for SQL Server 2005:

``````INSERT INTO tbl1 (SKU, Qty, US, JP, EU)
SELECT DISTINCT SKU, 0, 0, 0, 0 FROM tbl2
WHERE NOT EXISTS (SELECT 1 FROM tbl1 WHERE tbl1.SKU = tbl2.SKU)

UPDATE tbl1
SET Qty = source.US+source.JP+source.EU,
US = source.US,
JP = source.JP,
EU = source.EU
FROM tbl1
INNER JOIN (select SKU, ISNULL([US],0) US, ISNULL([JP],0) JP, ISNULL([EU],0) EU
from (select t1.SKU, t1.Qty, t1.DC
from tbl2 t1
inner join (select SKU, DC, MAX(OprDate) as MaxOprDate from tbl2 group by SKU, DC) t2
on t1.SKU = t2.SKU and t1.DC = t2.DC and t1.OprDate = t2.MaxOprDate) p
PIVOT (SUM(Qty) FOR DC IN (US, JP, EU)) pvt) as Source
ON tbl1.SKU = Source.SKU
``````

Here is the Unit Test:

``````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 #tbl1 values('XYZ',7,5,1,1)

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, US, JP, EU)
SELECT DISTINCT SKU, 0, 0, 0, 0 FROM #tbl2
WHERE NOT EXISTS (SELECT 1 FROM #tbl1 WHERE #tbl1.SKU = #tbl2.SKU)

UPDATE #tbl1
SET Qty = source.US+source.JP+source.EU,
US = source.US,
JP = source.JP,
EU = source.EU
FROM #tbl1
INNER JOIN (select SKU, ISNULL([US],0) US, ISNULL([JP],0) JP, ISNULL([EU],0) EU
from (select t1.SKU, t1.Qty, t1.DC
from #tbl2 t1
inner join (select SKU, DC, MAX(OprDate) as MaxOprDate from #tbl2 group by SKU, DC) t2
on t1.SKU = t2.SKU and t1.DC = t2.DC and t1.OprDate = t2.MaxOprDate) p
PIVOT (SUM(Qty) FOR DC IN (US, JP, EU)) pvt) as Source
ON #tbl1.SKU = Source.SKU

select * from #tbl1
``````
0

LVL 23

Expert Comment

ID: 35002362
Here is for SQL 2000 (Compatibility Level 80):

``````INSERT INTO tbl1 (SKU, Qty, US, JP, EU)
SELECT DISTINCT SKU, 0, 0, 0, 0 FROM tbl2
WHERE NOT EXISTS (SELECT 1 FROM tbl1 WHERE tbl1.SKU = tbl2.SKU)

UPDATE tbl1
SET Qty = source.US+source.JP+source.EU,
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
``````

Unit Test:

``````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 #tbl1 values('XYZ',7,5,1,1)

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, US, JP, EU)
SELECT DISTINCT SKU, 0, 0, 0, 0 FROM #tbl2
WHERE NOT EXISTS (SELECT 1 FROM #tbl1 WHERE #tbl1.SKU = #tbl2.SKU)

UPDATE #tbl1
SET Qty = source.US+source.JP+source.EU,
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
``````
0

LVL 13

Author Comment

ID: 35002595
wdosanjos,

It looks like this is going to work.
I will try it out.

0

LVL 13

Author Comment

ID: 35008629
wdosanjos,

It is not working right.
I don't want to update the Qty field (just the US, JP, and EU fields).
Based on your sample data from #tbl2, the expected result would be:

SKU    Qty      US       JP           EU
--------------------------------------------
ABC     5          2        3             0
DEF      3          1        1             1
GHI     10          3        4            3
``````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
``````
0

LVL 50

Expert Comment

ID: 35009725
ok try this

had an extraneous comma   on line 13
``````declare @contactid int
Update #tbl1
set us=x.us,jp=x.jp,eu=b.eu
from #tbl1
Inner join (
Select a.sku,b.us,b.jp,b.eu
,case when a.qty <> coalesce(b.us,0)
+coalesce(b.jp,0)+coalesce(b.eu,0)
then 'Mismatch'
else ''
end as checktotals
from #tbl1 as a
left outer join
(select sku
,MAX(case dc when 'US' then qty end) as US
,MAX(case dc when 'JP' then qty end) as JP
,MAX(case dc when 'EU' then qty end) as EU
from #tbl2 as z
where dc in ('US','JP','EU')
and not exists (select sku from #tbl2  as x
where x.sku=z.sku
and x.oprdate > z.oprdata
and x.dc=z.dc)
group by sku
) as x
on a.sku=x.sku
) as x
on #tbl1.sku=x.sku
and x.checktotals=''
``````
0

LVL 23

Expert Comment

ID: 35010218
I think your sample data for tbl2 does not match the expected result.  For example, SKU=GHI expects 4 on the JP column, but the sample tbl2 data has GHI, 7, JP, 2011-01-07.  Please verify.

I think based on the tbl2 sample data provided the result should be:

``````SKU	Qty	US	JP	EU
ABC	5	2 	3 	0
DEF	7	5 	1 	1
GHI	14	3 	7 	4
``````
0

LVL 13

Author Comment

ID: 35010619
wdosanjos,

Sorry for the confusion...

I don't know if this can be done.
What I need to do is to take n units, from the Qty colum and distribute them between US, JP, and EU:

SKU     Qty        US     JP      EU
----------------------------------------
ABC       5          ?        ?        ?
DEF        3          ?        ?        ?
GHI         10        ?       ?         ?

Take 5 ABC units::

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

Take 3 DEF units:

DEF      1      JP      2011-01-10 00:00:00     (1 from JP)
DEF      1      EU      2011-01-10 00:00:00     (1 from EU)
DEF      5      US      2011-01-09 00:00:00     (only 1 from US)

Take 10 GHI units:

GHI      3      US      2011-01-09 00:00:00     (3 from US)
GHI      4      EU      2011-01-08 00:00:00     (4 from EU)
GHI      7      JP      2011-01-07 00:00:00     (only 3 from JP)

Then my updated table should look like this:

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

``````Sample data:

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)

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

LVL 13

Author Comment

ID: 35010841
Correction:

Then my updated table should look like this:

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

LVL 23

Accepted Solution

wdosanjos earned 2000 total points
ID: 35012050

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

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 #tbl1 values('ABC',5,null,null,null)
insert into #tbl1 values('DEF',3,null,null,null)
insert into #tbl1 values('GHI',10,null,null,null)

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')

select ROW_NUMBER() over (order by #tbl2.SKU, #tbl2.OprDate desc) as seq,
#tbl2.*, #tbl1.Qty TotalQty, null As Reminder, null As UpdateQty
into #temp
from #tbl2
inner join #tbl1 on #tbl1.SKU = #tbl2.SKU
inner join
(select SKU, DC, MAX(OprDate) as MaxOprDate from #tbl2 group by SKU, DC) pmax
on pmax.SKU = #tbl2.SKU and pmax.DC = #tbl2.DC and pmax.MaxOprDate = #tbl2.OprDate
order by #tbl2.SKU, #tbl2.OprDate desc

UPDATE #temp
SET Reminder = t1.TotalQty - ISNULL((select SUM(qty) from #temp t2 where t1.SKU = t2.SKU and t1.seq > t2.seq), 0),
UpdateQty = case when t1.Reminder < t1.Qty then t1.Reminder else t1.Qty end
FROM #temp t1

UPDATE #temp
SET UpdateQty = case when Reminder < Qty then Reminder else Qty end

update #tbl1
set US = ISNULL((select updateqty from #temp tmp where tmp.SKU = t1.SKU and tmp.DC = 'US'), 0),
JP = ISNULL((select updateqty from #temp tmp where tmp.SKU = t1.SKU and tmp.DC = 'JP'), 0),
EU = ISNULL((select updateqty from #temp tmp where tmp.SKU = t1.SKU and tmp.DC = 'EU'), 0)
FROM #tbl1 t1

select * from #tbl1
``````
0

LVL 13

Author Comment

ID: 35012217
wdosanjos,

It looks like that might do it!
I will try it out.

0

LVL 13

Author Closing Comment

ID: 35012249
You're a true Expert!

Thank you.
0

LVL 13

Author Comment

ID: 35012274

Thanks again,
Rick
0

## Featured Post

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month12 days, 19 hours left to enroll