Combine Like Values After Joining Multiple Tables

[BBousman]

I have a SQL statement that I almost have working except for one thing, I need to add similar values....I know this can be done using the SUM statement but it won't exactly work how I want. Here's my code and the output:


SELECT Distinct Tbl.Job As 'Job Number', Tbl1.Promised_Quantity As 'Past Due',
Tbl2.Promised_Quantity As 'Current Week', Tbl3.Promised_Quantity As '1 Week Out',
Tbl4.Promised_Quantity As '2 Weeks Out'
FROM
(Select Delivery.Job from Delivery
Left Join Job on Delivery.Job = Job.Job
Where Job.Customer = '585'
and Delivery.Job like '%P') Tbl
Left Join
(Select Job, Promised_Quantity from Delivery
where Promised_Date < DateAdd(day, 7, GETDATE())
And Promised_Date > '2006-08-01'
And Job like '%P'
And Shipped_Date Is Null) Tbl1 On Tbl.Job = Tbl1.Job
Left JOIN
(Select Job, Promised_Quantity from Delivery
where Promised_Date >= DateAdd(day, 7, GETDATE())
And Promised_Date < DateAdd(day, 14, GetDate())
And Job like '%P'
And Shipped_Date Is Null) Tbl2 ON Tbl.Job = Tbl2.Job
Left Join
(Select Job, Promised_Quantity from Delivery
where Promised_Date >= DateAdd(day, 14, GetDate())
And Promised_Date < DateAdd(day, 21, GetDate())
And Job like '%P'
And Shipped_Date Is Null) Tbl3 On Tbl.Job = Tbl3.Job
Left Join
(Select Job, Promised_Quantity from Delivery
where Promised_Date >= DateAdd(day, 21, GetDate())
And Promised_Date < DateAdd(day, 28, GetDate())
And Job like '%P'
And Shipped_Date Is Null) Tbl4 On Tbl.Job = Tbl4.Job
Order By Tbl.Job

Job # WK 1 Wk 2 Wk 3 Wk 4
30322P NULL NULL NULL NULL
30323P 2200 NULL NULL NULL
30677P NULL NULL NULL NULL
30680P 4000 NULL NULL 2000
30680P 7500 NULL NULL 2000
30680P 15000 NULL NULL 2000

As you can see, there are 3 Job #'s of 30680P because there are 3 values for it in WK 1, I need to summarize all those for each week so it's only showing one 30680P in the output like below because there should only be one record of 2000 in WK 4 for 30680P. Can anyone help?

Job # WK 1 Wk 2 Wk 3 Wk 4
30322P NULL NULL NULL NULL
30323P 2200 NULL NULL NULL
30677P NULL NULL NULL NULL
30680P 26500 NULL NULL 2000

 

[Catadmin]

I'm not the best coder in the world but try this:

First, get rid of your DISTINCT keyword. That's going to hinder you in this case. Then try:

SELECT Distinct Tbl.Job As 'Job Number', Sum(isnull(Tbl1.Promised_Quantity,0)) As 'Past Due',
--Do the Sum(isnull()) for all your other fields
...
Group By Tbl.Job
Order By Tbl.Job

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"