Joining 3 tables and get the total summary

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining 3 tables and get the total summary

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-25 : 03:43:45

Guys,

I have 3 tables. the objective is to get the total summary of SKU from #table1 and name it as production Order. It should be distributed to per itemid of #Tale2 and indicated the total production order.

Then, get the ITEMID based on the PRODID of #table1 and #table2 and summarize per itemid. ProdOrder less the Usage the result will be the reclaim. can you help me guys to modify the scripts based on the sample below. Thanks.

Select
p.NAME,
p.SKU,
p.ASUPRODLINEID,
j.ITEMID,
i.ITEMNAME,
SUM(j.INVENTCONSUMP)as USAGE
From #TABLE1 p with (nolock)
Left Outer Join #TABLE2 j with (nolock)
On p.PRODID = j.PRODID
Left Outer Join #TABLE3 i with (nolock)
On j.ITEMID = i.ITEMID
where p.FINISHEDDATE >='2011-12-01' and p.FINISHEDDATE <'2012-01-01'
and p.ITEMID='P300-4410-THUNDRBLT4G-U'
and p.ASUPRODLINEID='Main'
Group by p.ITEMID,
p.ASUPRODLINEID,
j.ITEMID,
p.NAME,
i.ITEMNAME

SAMPLE DATA THE SAME WITH MY DB

CREATE TABLE #Table1 (SKU nvarchar(50), ASUPRODLINEID nvarchar(20), FINISHDATE datetime, PRODID nvarchar(20))
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-22','TRC001194693')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-16','TRC001194694')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','011-12-19','TRC001194695')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','011-12-16','TRC001194696')

CREATE TABLE #Table2 (ITEMID nvarchar(50), PRODID nvarchar(20), INVENTCONSUMP int)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194696',1)

CREATE TABLE #Table3 (ITEMID nvarchar(50), NAME nvarchar(30))
INSERT INTO #Table3(ITEMID,PRODID,NAME) VALUES ('HTC1159','5300BLA A Cover')
INSERT INTO #Table3(ITEMID,PRODID,NAME) VALUES ('HTC1257','Audiox 8900 LCD')
[code]
[\code]

A.)The result be like this format:
SKU-----------------------ITEMID-----NAME-------------ProdOrder----Reclaim--Usage---Total
-----------------------------------------------------------------------------------------
P300-4410-THUNDRBLT4G-U---HTC1159----5300BLA A Cover-----4--------------0-------4------4
P300-4410-THUNDRBLT4G-U---HTC1257----Audiox 8900 LCD-----4--------------1-------3------3

B.)
SKU-----------------------ITEMID-----ProdOrder----USAGE
-------------------------------------------------------
P300-4410-THUNDRBLT4G-U---HTC1159----4-------------4
P300-4410-THUNDRBLT4G-U---HTC1257----4-------------4

Thanks and regards,

JOV

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 06:06:59

I'm not clear about
what is Total
what is Usage
what is Reclaim
so I'm not able to give a complete solution.

But maybe this is on the right way...

CREATE TABLE #Table1 (SKU nvarchar(50), ASUPRODLINEID nvarchar(20), FINISHDATE datetime, PRODID nvarchar(20))
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-22','TRC001194693')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-16','TRC001194694')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-19','TRC001194695')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-16','TRC001194696')

CREATE TABLE #Table2 (ITEMID nvarchar(50), PRODID nvarchar(20), INVENTCONSUMP int)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194696',1)


CREATE TABLE #Table3 (ITEMID nvarchar(50), NAME nvarchar(30))
INSERT INTO #Table3(ITEMID,NAME) VALUES ('HTC1159','5300BLA A Cover')
INSERT INTO #Table3(ITEMID,NAME) VALUES ('HTC1257','Audiox 8900 LCD')

select
SKU,
ITEMID,
NAME,
max(ProdOrder) as ProdOrder,
sum(INVENTCONSUMP) as Usage
from
(
select 
t1.SKU,
t2.ITEMID,
t2.INVENTCONSUMP,
t3.NAME,
POC.cnt as ProdOrder
from #Table1 as t1
join (select SKU, count(*) as cnt from #Table1 group by SKU) as POC on POC.SKU = t1.SKU
join #Table2 as t2 on t2.PRODID = t1.PRODID
join #Table3 as t3 on t3.ITEMID = t2.ITEMID
) as dt
group by SKU,ITEMID,NAME


drop table #Table1
drop table #Table2
drop table #Table3

gives:
SKU                                                ITEMID                                             NAME                           ProdOrder   Usage
-------------------------------------------------- -------------------------------------------------- ------------------------------ ----------- -----------
P300-4410-THUNDRBLT4G-U                            HTC1159                                            5300BLA A Cover                4           4
P300-4410-THUNDRBLT4G-U                            HTC1257                                            Audiox 8900 LCD                4           3

No, you're never too old to Yak'n'Roll if you're too young to die.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-25 : 19:43:31

Hi WebFred,

Thank you for the reply and you got It. cheers for you guys.
This is what im looking for but need additional info to be included in the query.

1. The reclaim is the result of subtracting the ProdOrder Less Usage. the reclaim will be 0 and 1.
2. Total is the the same as Usage.
3. Where should i place the Where Clause? I have to obtain and validate by Finishdate.

select
SKU,
ITEMID,
NAME,
max(ProdOrder) as ProdOrder,
sum(INVENTCONSUMP) as Usage
from
(
select
t1.SKU,
t2.ITEMID,
t2.INVENTCONSUMP,
t3.NAME,
POC.cnt as ProdOrder
from #Table1 as t1
join (select SKU, count(*) as cnt from #Table1 group by SKU) as POC on POC.SKU = t1.SKU
join #Table2 as t2 on t2.PRODID = t1.PRODID
join #Table3 as t3 on t3.ITEMID = t2.ITEMID
) as dt
group by SKU,ITEMID,NAME

Regards,

JOV

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-26 : 03:39:53

[code]CREATE TABLE #Table1 (SKU nvarchar(50), ASUPRODLINEID nvarchar(20), FINISHDATE datetime, PRODID nvarchar(20))
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-22','TRC001194693')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-16','TRC001194694')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-19','TRC001194695')
INSERT INTO #Table1(SKU,ASUPRODLINEID,FINISHDATE,PRODID) VALUES ('P300-4410-THUNDRBLT4G-U','Main','2011-12-16','TRC001194696')

CREATE TABLE #Table2 (ITEMID nvarchar(50), PRODID nvarchar(20), INVENTCONSUMP int)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194693',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194694',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1257','TRC001194695',1)
INSERT INTO #Table2(ITEMID,PRODID,INVENTCONSUMP) VALUES ('HTC1159','TRC001194696',1)

CREATE TABLE #Table3 (ITEMID nvarchar(50), NAME nvarchar(30))
INSERT INTO #Table3(ITEMID,NAME) VALUES ('HTC1159','5300BLA A Cover')
INSERT INTO #Table3(ITEMID,NAME) VALUES ('HTC1257','Audiox 8900 LCD')

select
SKU,
ITEMID,
NAME,
max(ProdOrder) as ProdOrder,
sum(INVENTCONSUMP) as Usage,
max(ProdOrder) - sum(INVENTCONSUMP) as Reclaim,
sum(INVENTCONSUMP) as Total
from
(
select
t1.SKU,
t2.ITEMID,
t2.INVENTCONSUMP,
t3.NAME,
POC.cnt as ProdOrder
from #Table1 as t1
join (select SKU, count(*) as cnt
from #Table1
where (FINISHDATE >='20111201' and FINISHDATE <'20120101')
and SKU = 'P300-4410-THUNDRBLT4G-U'
and ASUPRODLINEID = 'Main'
group by SKU) as POC on POC.SKU = t1.SKU
join #Table2 as t2 on t2.PRODID = t1.PRODID
join #Table3 as t3 on t3.ITEMID = t2.ITEMID
) as dt
group by SKU,ITEMID,NAME

drop table #Table1
drop table #Table2
drop table #Table3

gives:
SKU ITEMID NAME ProdOrder Usage Reclaim Total
------------------------- ---------- ------------------- ----------- ----------- ----------- -----------
P300-4410-THUNDRBLT4G-U HTC1159 5300BLA A Cover 4 4 0 4
P300-4410-THUNDRBLT4G-U HTC1257 Audiox 8900 LCD 4 3 1 3

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-26 : 21:52:54

Thank you very much webfred..

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-30 : 02:37:59

Running this query i got 500,000 records which is very high.

select
t1.SKU,
t2.ITEMID,
t2.INVENTCONSUMP,
t3.NAME,
POC.cnt as ProdOrder
from #Table1 as t1
join (select SKU, count(*) as cnt
from #Table1
where (FINISHDATE >='20111201' and FINISHDATE <'20120101')
and SKU = 'P300-4410-THUNDRBLT4G-U'
and ASUPRODLINEID = 'Main'
group by SKU) as POC on POC.SKU = t1.SKU
join #Table2 as t2 on t2.PRODID = t1.PRODID
join #Table3 as t3 on t3.ITEMID = t2.ITEMID

But running this sub query i got 184 records which is correct.

select SKU, count(*) as cnt
from #Table1
where (FINISHDATE >='20111201' and FINISHDATE <'20120101')
and SKU = 'P300-4410-THUNDRBLT4G-U'
and ASUPRODLINEID = 'Main'
group by SKU

How could i pullout the 184 records derived from this sub query to #table2 using PRODID.

This scripts join #Table2 as t2 on t2.PRODID = t1.PRODID give me 500,000 records. thanks.

[code]
[\code]

regards,
JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-30 : 02:58:37

Guys,

can you please help me to simlifyn this query.
I just add another join.

select
SKU,
ITEMID,
NAME,
max(ProdOrder) as ProdOrder,
sum(INVENTCONSUMP) as Usage,
max(ProdOrder) - sum(INVENTCONSUMP) as Reclaim,
sum(INVENTCONSUMP) as Total
from
(
select
t1.SKU,
t2.ITEMID,
t2.INVENTCONSUMP,
t3.NAME,
POC.cnt as ProdOrder
from #Table1 as t1
join (select SKU, count(*) as cnt
from #Table1
where (FINISHDATE >='20111201' and FINISHDATE <'20120101')
and SKU = 'P300-4410-THUNDRBLT4G-U'
and ASUPRODLINEID = 'Main'
group by SKU) as POC on POC.SKU = t1.SKU
Join (select PRODID
from #Table1
where (FINISHEDDATE >='01-30-2012' and FINISHEDDATE <='01-30-2012')
) as PRO on PRO.PRODID = t1.PRODID
join #Table3 as t3 on t3.ITEMID = t2.ITEMID
) as dt
group by SKU,ITEMID,NAME

Thanks.

JOV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources