Rick
asked on
Help with SQL query
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!
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!
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
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=''
Please try the following. It updates or adds SKU records to 'tbl1'.
Here is the unit test:
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
ASKER
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."
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."
ASKER
Lowfatspread,
Same thing :)
Same thing :)
what compatability level are you running the database at?
the old method
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=''
ASKER
Lowfatspread,
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'.
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'.
Here is the revised script for SQL Server 2005:
Here is the Unit Test:
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
Here is for SQL 2000 (Compatibility Level 80):
Unit Test:
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
ASKER
wdosanjos,
It looks like this is going to work.
I will try it out.
- Obrigado
It looks like this is going to work.
I will try it out.
- Obrigado
ASKER
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
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
ok try this
had an extraneous comma on line 13
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=''
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:
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
ASKER
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wdosanjos,
It looks like that might do it!
I will try it out.
- Obrigado.
It looks like that might do it!
I will try it out.
- Obrigado.
ASKER
You're a true Expert!
Thank you.
Thank you.
ASKER
Open in new window