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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-14 : 08:15:30
|
| rob writes "I am trying to get this query to pull out rows where the customer has part installed work, ie the customer has many orders but the order is not complete until all work is installed, so for example the customer may have 3 jobs but if one of the jobs has an Installed Date of NULL but at least one job is installed then i need the record to come out.here is my query so far, but i dont know how to count how many of the jobs are not installed???SELECT TOP 100 PERCENT dbo.Customers.CustomerID, COUNT(dbo.Sales.CustomerID) AS JobCountFROM dbo.Customers INNER JOIN dbo.Sales ON dbo.Customers.CustomerID = dbo.Sales.CustomerID INNER JOIN dbo.SaleItems ON dbo.Sales.SaleID = dbo.SaleItems.SaleIDWHERE (dbo.SaleItems.TypeOfJob <> 7) AND (dbo.SaleItems.InstalledDate IS NULL) AND (dbo.SaleItems.CancelledDate IS NULL)GROUP BY dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.LastName, dbo.Customers.HouseNo, dbo.Customers.Street, dbo.Customers.Area, dbo.Customers.City, dbo.Customers.County, dbo.Customers.PostcodeHAVING (COUNT(dbo.Sales.CustomerID) > 1)ORDER BY dbo.Customers.CustomerID" |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
|
|
make my weekend 