SQL Server Performance

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

Discussion in 'General Developer Questions' started by robmays, Oct 14, 2005.

  1. robmays New Member

    "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
  2. FrankKalis Moderator

  3. robmays New Member

    still give an error
  4. FrankKalis Moderator

  5. Madhivanan Moderator

    Instead of

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

    Try

    (count(SaleItems.InstalledDate)> 0)



    Madhivanan

    Failing to plan is Planning to fail
  6. robmays New Member

    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.
  7. Madhivanan Moderator

    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  8. robmays New Member

    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
  9. mmarovic Active Member

    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.
  10. Madhivanan Moderator

  11. robmays New Member

    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
  12. Adriaan New Member

    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 *.
  13. robmays New Member

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

    Sorry, I edited original post to fix the error.
  15. robmays New Member

    mmarovic still brings out customers with jobs where none are installed, they mus have at least one job installed
  16. Adriaan New Member

    Rob,

    Seems like you've overlooked my previous post ...
  17. mmarovic Active Member

    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.
  18. robmays New Member

    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
  19. mmarovic Active Member

    np, I'm glad you have your solution.

Share This Page