(count(SaleItems.InstalledDate = null)> 0) error

"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 JobCount,SaleItems.InstalledDate
FROM dbo.Customers INNER JOIN
dbo.Sales ON dbo.Customers.CustomerID = dbo.Sales.CustomerID INNER JOIN
dbo.SaleItems ON dbo.Sales.SaleID = dbo.SaleItems.SaleID
WHERE (dbo.SaleItems.TypeOfJob <> 7) 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.Postcode,SaleItems.InstalledDate
HAVING (COUNT(dbo.Sales.CustomerID) > 1) and (count(SaleItems.InstalledDate = null)> 0)
ORDER BY dbo.Customers.CustomerID

still give an error

it does not like the = or the IS in the parameter
(count(SaleItems.InstalledDate = null)> 0)
(count(SaleItems.InstalledDate is null)> 0)

my problem is there must be at least one job installed and at least one job not installed per customer.

if i try that (count(SaleItems.InstalledDate)> 0)

it will still return customers where all jobs are not installed BUT for a part order there must be at least one job installed and one NOT.

so if a customer has 5 jobs and one is installed only then it is a part oreder

but if customer B has 4 jobs and none are installed then it is not a part order so i dont want to see that in my result

Try:
SELECT dbo.Customers.CustomerID,
COUNT(dbo.Sales.CustomerID) AS JobCount,
SaleItems.InstalledDate
FROM dbo.Customers
JOIN dbo.Sales ON dbo.Customers.CustomerID = dbo.Sales.CustomerID
JOIN dbo.SaleItems ON dbo.Sales.SaleID = dbo.SaleItems.SaleID
WHERE (dbo.SaleItems.TypeOfJob <> 7)
AND (dbo.SaleItems.CancelledDate IS NULL)
GROUP BY dbo.Customers.CustomerID,
SaleItems.InstalledDate
HAVING COUNT(dbo.Sales.CustomerID) > 1 and count(SaleItems.InstalledDate) < count(*)
ORDER BY dbo.Customers.CustomerID
Also, consider using table aliases. Count(column) returns count of rows where column value is not null.

sample data returned

CustomerID JobCount InstalledDate
1 3 null
2 4 null
3 2 null


the problem here is that customer 2 has no jobs installed yet so it should not be retruned
Customer 1 + 2 both have at least one job installed

This stuff is easy just as long as you don't think about it too hard ...

WHERE
-- Sale must have a SaleItem with an install date
Sales.SaleId IN (SELECT SaleId FROM SaleItem WHERE InstallDate IS NOT NULL)
AND
-- Sale must also have a SaleItem without an install date
Sales.SaleId IN (SELECT SaleId FROM SaleItem WHERE InstallDate IS NULL)

Forget about the COUNT(InstallDate IS NULL) stuff, just use an *.

for mmarovic
i get a
Line 8: Incorrect syntax near ')'.

Sorry, I edited original post to fix the error.

mmarovic still brings out customers with jobs where none are installed, they mus have at least one job installed

Rob,

Seems like you've overlooked my previous post ...

I didn't really read your explanations, i've just translated the logic from your pseudo query. I guess you need to add
and count(SaleItems.InstalledDate) > 1
in having clause.

Also take a look at Adriaan's solution. I'm not quite sure how does your table structure look like, but that may be another (maybe better) way to go.

take my hat off to this forum i think adriaan got me on the road cheers and thankyou mmarovic as well but your example stil brought out customers with orders and none were installed.

a lot happier now

np, I'm glad you have your solution.