Subquery question - 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

General SQL Server Forums

New to SQL Server Programming

Subquery question

Author

Topic

jrussell999
Starting Member

14 Posts

Posted - 2009-11-25 : 11:10:36

I want to do a pretty basic SELECT query, with a twist: the last field returned should be the latest order date. I think this has to be done with a MAX function in a subquery, but I'm not sure how to do that.

The table tblServiceOrders can contain any number of orders for an account, from zero to many, and I want to return the date of the latest order, or null if there isn't one.

I've been looking around for the answer to this, but I don't quite get it yet. Here's what I've got so far:

SELECT     dbo.tblAccounts.AccountNumber, dbo.tblAccounts.AccountName, dbo.tblAccounts.Address1, dbo.tblAccounts.Address2, dbo.tblAccounts.City, 
                      dbo.tblAccounts.State, dbo.tblAccounts.PostalCode, dbo.tblAccounts.Country, dbo.tblReps.RepName, dbo.tblAccounts.AccountID, 
                      dbo.tblAccounts.AccountType, dbo.tblAccounts.InitialContact, dbo.tblAccounts.IsVendor, dbo.tblAccounts.IsSubcontractor, 
                      dbo.tblAccounts.PrimaryPhoneNumber, dbo.tblAccounts.PrimaryFaxNumber, dbo.tblContacts.ContactName, dbo.tblAccounts.PrimaryContactNumber, 
                      dbo.tblAccounts.ARCustomerNumber, dbo.tblAltBillTo.Name, dbo.tblAltBillTo.Address1 AS Expr1, dbo.tblAltBillTo.Address2 AS Expr2, 
                      dbo.tblAltBillTo.City AS Expr3, dbo.tblAltBillTo.State AS Expr4, dbo.tblAltBillTo.PostalCode AS Expr5, dbo.tblAccounts.SalesTaxCode, 
                      dbo.tblAccounts.TaxExemptID, dbo.tblAccounts.TaxExemptCategory, MAX(dbo.tblServiceOrders.InvoiceDate) as LastSODate
FROM         dbo.tblAccounts INNER JOIN
                      dbo.tblAltBillTo ON dbo.tblAccounts.AccountNumber = dbo.tblAltBillTo.AccountNumber INNER JOIN
                      dbo.tblReps ON dbo.tblAccounts.RepNumber = dbo.tblReps.RepNumber INNER JOIN
                      dbo.tblContacts ON dbo.tblAccounts.AccountNumber = dbo.tblContacts.AccountNumber INNER JOIN
                      dbo.tblServiceOrders ON dbo.tblAccounts.AccountNumber = dbo.tblServiceOrders.AccountNumber
ORDER BY dbo.tblAccounts.AccountNumber

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:00:01

Try this...

SELECT dbo.tblAccounts.AccountNumber
, dbo.tblAccounts.AccountName
, dbo.tblAccounts.Address1
, dbo.tblAccounts.Address2
, dbo.tblAccounts.City
,dbo.tblAccounts.State
, dbo.tblAccounts.PostalCode
, dbo.tblAccounts.Country
, dbo.tblReps.RepName
, dbo.tblAccounts.AccountID
, dbo.tblAccounts.AccountType
, dbo.tblAccounts.InitialContact
, dbo.tblAccounts.IsVendor
, dbo.tblAccounts.IsSubcontractor
, dbo.tblAccounts.PrimaryPhoneNumber
, dbo.tblAccounts.PrimaryFaxNumber
, dbo.tblContacts.ContactName
, dbo.tblAccounts.PrimaryContactNumber
, dbo.tblAccounts.ARCustomerNumber
, dbo.tblAltBillTo.Name
, dbo.tblAltBillTo.Address1 AS Expr1
, dbo.tblAltBillTo.Address2 AS Expr2
, dbo.tblAltBillTo.City AS Expr3
, dbo.tblAltBillTo.State AS Expr4
, dbo.tblAltBillTo.PostalCode AS Expr5
, dbo.tblAccounts.SalesTaxCode
, dbo.tblAccounts.TaxExemptID
, dbo.tblAccounts.TaxExemptCategory
, COALESCE(A.InvoiceDate,0) as LastSODate
FROM  dbo.tblAccounts INNER JOIN
dbo.tblAltBillTo ON dbo.tblAccounts.AccountNumber = dbo.tblAltBillTo.AccountNumber INNER JOIN
dbo.tblReps ON dbo.tblAccounts.RepNumber = dbo.tblReps.RepNumber INNER JOIN
dbo.tblContacts ON dbo.tblAccounts.AccountNumber = dbo.tblContacts.AccountNumber LEFT JOIN
(SELECT MAX(InvoiceDate) AS InvoiceDate,AccountNumber FROM dbo.tblServiceOrders GROUP BY AccountNumber) A ON dbo.tblAccounts.AccountNumber = A.AccountNumber
ORDER BY dbo.tblAccounts.AccountNumber

mfemenel
Professor Frink

1421 Posts

Posted - 2009-11-25 : 12:03:09

SELECT dbo.tblAccounts.AccountNumber, dbo.tblAccounts.AccountName, dbo.tblAccounts.Address1, dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.State, dbo.tblAccounts.PostalCode, dbo.tblAccounts.Country, dbo.tblReps.RepName, dbo.tblAccounts.AccountID,
dbo.tblAccounts.AccountType, dbo.tblAccounts.InitialContact, dbo.tblAccounts.IsVendor, dbo.tblAccounts.IsSubcontractor,
dbo.tblAccounts.PrimaryPhoneNumber, dbo.tblAccounts.PrimaryFaxNumber, dbo.tblContacts.ContactName, dbo.tblAccounts.PrimaryContactNumber,
dbo.tblAccounts.ARCustomerNumber, dbo.tblAltBillTo.Name, dbo.tblAltBillTo.Address1 AS Expr1, dbo.tblAltBillTo.Address2 AS Expr2,
dbo.tblAltBillTo.City AS Expr3, dbo.tblAltBillTo.State AS Expr4, dbo.tblAltBillTo.PostalCode AS Expr5, dbo.tblAccounts.SalesTaxCode,
dbo.tblAccounts.TaxExemptID, dbo.tblAccounts.TaxExemptCategory, maxInvoice.LastSODate
FROM dbo.tblAccounts INNER JOIN
dbo.tblAltBillTo ON dbo.tblAccounts.AccountNumber = dbo.tblAltBillTo.AccountNumber INNER JOIN
dbo.tblReps ON dbo.tblAccounts.RepNumber = dbo.tblReps.RepNumber INNER JOIN
dbo.tblContacts ON dbo.tblAccounts.AccountNumber = dbo.tblContacts.AccountNumber
LEFT JOIN
(Select max(InvoiceDate)as LastSODate,AccountNumber
from ServiceOrders
)MaxInvoice
On tblAccounts.AccountNumber=MaxInvoice.AccountNumber
ORDER BY dbo.tblAccounts.AccountNumber

Mike
"oh, that monkey is going to pay"

jrussell999
Starting Member

14 Posts

Posted - 2009-11-25 : 14:00:00

Mfemenel, I tried your version first because it looked easier to understand, but when I verified the syntax there was an error.

Vijayisonly, I think your version works. But it seems to return only accounts that have at least one order. How would this be modified to include accounts with no orders?

There was another surprise as well. There are multiple values per account in the dbo.tblContacts and dbo.tblAltBillTo tables. I probably should have seen this coming, because of the one-to-many relationships between those tables and tblAccounts. This brings up the question, how do people deal with multiple values for text fields when there shouldn't be multiple values? Is there any good answer to this? Ultimately, my boss will answer this question, but I'd like to know what others do.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-11-25 : 14:12:58

Use Left Join and you will get Nulls for information from Order tables for account that has no orders...

jrussell999
Starting Member

14 Posts

Posted - 2009-11-25 : 14:51:37

Vijayisonly's version uses a Left Join, though. BIDS modified this to "Left Outer Join" when I verified the syntax, but that shouldn't matter, right? I came across the following:

If all arguments are NULL, COALESCE returns NULL. Note: At least one of the null values must be a typed NULL.
http://msdn.microsoft.com/en-us/library/ms190349.aspx

I think this might be the problem. If there are no orders, there just won't be any record corresponding to that account in the Orders table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources