Subquery possible here? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery possible here?

Author

Topic

catherinemm
Starting Member

2 Posts

Posted - 2009-02-13 : 12:14:09

I'm trying to generate two values but they are based on an inner and an outer join. I'm not even sure this is possible to run in one query, but I'd certainly like to try or get any feedback on the best way to approach this.

Scenario:
Tables: Jobs, JobDetails, Invoices, Materials

The JobsDetails table contains a field called ULPounds. Based on a given date, I'd like to generate the TotalPounds generated for each material. BUT, my client would also like to show the TotalPoundsInvoiced in the next column. So, TotalPounds is an Outer Left Join, but TotalPoundsInvoiced is an Inner Join.

Here is my original SQL statement showing just the TotalPounds. Is there a way to also generate the Inner Join in a subquery to show TotalPoundsInvoiced? (Actually the Invoices table is not even relevant to this query--same results with or without it but am showing it for the example).

SELECT TOP (100) PERCENT SUM(dbo.JobDetails.ULPounds) AS SumofPounds, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID, SUM(dbo.JobDetails.Amount) AS SumofAmount
FROM dbo.Jobs INNER JOIN
dbo.JobDetails ON dbo.Jobs.JobID = dbo.JobDetails.JobID INNER JOIN
dbo.Materials ON dbo.JobDetails.MaterialID = dbo.Materials.MaterialID INNER JOIN
dbo.MaterialCat ON dbo.Materials.MaterialCatID = dbo.MaterialCat.MaterialCatID LEFT OUTER JOIN
dbo.Invoices ON dbo.Jobs.InvID = dbo.Invoices.InvID
WHERE (dbo.Jobs.UnloadDate >= CONVERT(DATETIME, '2009-02-13', 102)) AND (dbo.Jobs.UnloadDate <= CONVERT(DATETIME, '2009-02-13', 102))
GROUP BY dbo.Jobs.StatusID, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID
HAVING (SUM(dbo.JobDetails.ULPounds) <> 0) AND (dbo.Jobs.StatusID = 'C')
ORDER BY dbo.MaterialCat.MaterialCat

Thanks in advance,
Catherine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:38:17

please provide some sample data and explain what you want as output

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

catherinemm
Starting Member

2 Posts

Posted - 2009-02-13 : 14:06:02

I think I'm making this more complicated than it needs to be. Removing the joins, here is the query:

SELECT     TOP (100) PERCENT SUM(dbo.JobDetails.ULPounds) AS SumofPounds, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID, SUM(dbo.JobDetails.Amount) AS SumofAmount
FROM dbo.Jobs INNER JOIN dbo.JobDetails ON dbo.Jobs.JobID = dbo.JobDetails.JobID INNER JOIN dbo.Materials ON dbo.JobDetails.MaterialID = dbo.Materials.MaterialID INNER JOIN dbo.MaterialCat ON dbo.Materials.MaterialCatID = dbo.MaterialCat.MaterialCatID
WHERE     (dbo.Jobs.UnloadDate >= CONVERT(DATETIME, '2009-02-13', 102)) AND (dbo.Jobs.UnloadDate <= CONVERT(DATETIME, '2009-02-13', 102))
GROUP BY dbo.Jobs.StatusID, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID
HAVING      (SUM(dbo.JobDetails.ULPounds) <> 0) AND (dbo.Jobs.StatusID = 'C')
ORDER BY dbo.MaterialCat.MaterialCat

Results:

SumofPounds  MaterialCat         MaterialCatID SumofAmount
160          Batteries and Bulbs  14           NULL
21460        Construction          7           416.00
5802         Difficult             9            85.00
315          Electronics          13            45.00
30575        Metal                 2            12.00
5400         Paper                 1           NULL
400          Wood                  6           NULL

What I also want to show (here's where I need the help) is the SumofPoundsInvoiced. In order to do this, the WHERE statement would also include:
InvID Is Not NULL

Results:

PoundsInvoiced MaterialCat     MaterialCatID SumofAmount
9500           Construction    7	     416.00
575            Difficult       9              85.00
105            Electronics     13             45.00
345            Metal           2              12.00

Desired Output:


Pounds Pounds MaterialCat              MaterialCatID  SumofAmount
       Invoiced
160	0     Batteries and Bulbs                  14  NULL
21460	9500  Construction	                    7  416.00
5802	575   Difficult                             9	85.00
315	105   Electronics                          13	45.00
30575	345   Metal                                 2	12.00
5400    0     Paper                                 1	NULL
400     0     Wood                                  6	NULL

Does that make any sense? Apologies if I'm not being clear...

TIA,
Catherine

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 15:59:40

quote:

Originally posted by catherinemm

I think I'm making this more complicated than it needs to be. Removing the joins, here is the query:

SELECT     TOP (100) PERCENT SUM(dbo.JobDetails.ULPounds) AS SumofPounds, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID, SUM(dbo.JobDetails.Amount) AS SumofAmount
FROM dbo.Jobs INNER JOIN dbo.JobDetails ON dbo.Jobs.JobID = dbo.JobDetails.JobID INNER JOIN dbo.Materials ON dbo.JobDetails.MaterialID = dbo.Materials.MaterialID INNER JOIN dbo.MaterialCat ON dbo.Materials.MaterialCatID = dbo.MaterialCat.MaterialCatID
WHERE     (dbo.Jobs.UnloadDate >= CONVERT(DATETIME, '2009-02-13', 102)) AND (dbo.Jobs.UnloadDate <= CONVERT(DATETIME, '2009-02-13', 102))
GROUP BY dbo.Jobs.StatusID, dbo.MaterialCat.MaterialCat, dbo.MaterialCat.MaterialCatID
HAVING      (SUM(dbo.JobDetails.ULPounds) <> 0) AND (dbo.Jobs.StatusID = 'C')
ORDER BY dbo.MaterialCat.MaterialCat

Results:

SumofPounds  MaterialCat         MaterialCatID SumofAmount
160          Batteries and Bulbs  14           NULL
21460        Construction          7           416.00
5802         Difficult             9            85.00
315          Electronics          13            45.00
30575        Metal                 2            12.00
5400         Paper                 1           NULL
400          Wood                  6           NULL

What I also want to show (here's where I need the help) is the SumofPoundsInvoiced. In order to do this, the WHERE statement would also include:
InvID Is Not NULL

Results:

PoundsInvoiced MaterialCat     MaterialCatID SumofAmount
9500           Construction    7	     416.00
575            Difficult       9              85.00
105            Electronics     13             45.00
345            Metal           2              12.00

Desired Output:


Pounds Pounds MaterialCat              MaterialCatID  SumofAmount
       Invoiced
160	0     Batteries and Bulbs                  14  NULL
21460	9500  Construction	                    7  416.00
5802	575   Difficult                             9	85.00
315	105   Electronics                          13	45.00
30575	345   Metal                                 2	12.00
5400    0     Paper                                 1	NULL
400     0     Wood                                  6	NULL

Does that make any sense? Apologies if I'm not being clear...

TIA,
Catherine

Where is the sample data and Explanation? Please read link sent by visakh properly ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources