none
how to count results of a case clause

    Question

  • hello, not sure if its possible with what i have here but i am trying to dither down this information to result in a table to count the amount of results from the CASE clause.  i basically want a result of 3 columns titled on time, OVERDUE, and Due Today and a count of each of those. Below is my query. please let me know if you need info about data types for this, not sure if you do and i know its not great T-SQL but im just learning so any guidance would be much appreciated.

    SELECT (convert (varchar(10), oi.OrderDate, 110)) as RequestDate, 
    o.LoanNumber AS AppNo, oc.LastName, oc.FirstName, p.Abbreviation as Description,
    CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))) as 'Overdue By',
    o.City, o.State, co.Name AS County, m.Description AS LastStatus,
    (convert (varchar(10), oi.RequiredByDate, 110)) as DueDate,
    CASE
    WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
    WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
    ELSE 'On Time'
    END AS 'Delivery Status'
    FROM OrderItems oi (NOLOCK)
    JOIN Orders o (NOLOCK) ON o.OrderID = oi.OrderID
    JOIN Products p (NOLOCK) ON p.ProductID = oi.ProductID
    JOIN Counties co (NOLOCK) ON co.FIPS = o.FIPS
    JOIN Milestones m (NOLOCK) ON m.MilestoneID = oi.LastMilestoneID
    LEFT JOIN PropertyTypes pt (NOLOCK) ON pt.PropertyTypeID = o.PropertyTypeID
    LEFT JOIN OrderContacts oc (NOLOCK) ON oc.OrderID = oi.OrderID AND oc.ContactTypeID = 11
    WHERE oi.QueueID > 0
    and (convert (varchar(10), oi.RequiredByDate, 110)) <= (convert (varchar(10), GETDATE(), 110))

    Monday, April 16, 2012 12:44 AM

Answers

  • Change the group by to this:  Missed removing an AS

    GROUP BY 
     (convert (varchar(10), oi.OrderDate, 110)), 
     o.LoanNumber, 
    oc.LastName, 
    oc.FirstName, 
    p.Abbreviation ,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))),
     o.City, o.State, co.Name, m.Description,
     (convert (varchar(10), oi.RequiredByDate, 110)) ,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END
    


    Chuck

    Monday, April 16, 2012 1:12 AM

All replies

  • SELECT (convert (varchar(10), oi.OrderDate, 110)) as RequestDate, 
     o.LoanNumber AS AppNo, oc.LastName, oc.FirstName, p.Abbreviation as Description,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))) as 'Overdue By',
     o.City, o.State, co.Name AS County, m.Description AS LastStatus,
     (convert (varchar(10), oi.RequiredByDate, 110)) as DueDate,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END AS 'Delivery Status',
     SUM(CASE  WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END) as DueTodayCount,
     SUM(CASE  WHEN GETDATE() > oi.RequiredByDate THEN 1 ELSE 0 END) as OnTImeCount,
     SUM(CASE  WHEN (dbo.TruncateDate(GETDATE()) != dbo.TruncateDate(oi.RequiredByDate) AND GETDATE() <= oi.RequiredByDate) THEN 1 ELSE 0 END) as ONTIMECount
    
    
    
     FROM OrderItems oi (NOLOCK)
     JOIN Orders o (NOLOCK) ON o.OrderID = oi.OrderID
     JOIN Products p (NOLOCK) ON p.ProductID = oi.ProductID
     JOIN Counties co (NOLOCK) ON co.FIPS = o.FIPS
     JOIN Milestones m (NOLOCK) ON m.MilestoneID = oi.LastMilestoneID
     LEFT JOIN PropertyTypes pt (NOLOCK) ON pt.PropertyTypeID = o.PropertyTypeID
     LEFT JOIN OrderContacts oc (NOLOCK) ON oc.OrderID = oi.OrderID AND oc.ContactTypeID = 11
     WHERE oi.QueueID > 0
     and (convert (varchar(10), oi.RequiredByDate, 110)) <= (convert (varchar(10), GETDATE(), 110)) 


    Chuck


    Monday, April 16, 2012 12:50 AM
  • thank you but i am still getting an error with this.

    Msg 8120, Level 16, State 1, Line 1
    Column 'OrderItems.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Monday, April 16, 2012 12:55 AM
  • SELECT (convert (varchar(10), oi.OrderDate, 110)) as RequestDate, 
     o.LoanNumber AS AppNo, oc.LastName, oc.FirstName, p.Abbreviation as Description,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))) as 'Overdue By',
     o.City, o.State, co.Name AS County, m.Description AS LastStatus,
     (convert (varchar(10), oi.RequiredByDate, 110)) as DueDate,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END AS 'Delivery Status',
     SUM(CASE  WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END) as DueTodayCount,
     SUM(CASE  WHEN GETDATE() > oi.RequiredByDate THEN 1 ELSE 0 END) as OnTImeCount,
     SUM(CASE  WHEN (dbo.TruncateDate(GETDATE()) != dbo.TruncateDate(oi.RequiredByDate) AND GETDATE() <= oi.RequiredByDate) THEN 1 ELSE 0 END) as ONTIMECount
    
    
    
     FROM OrderItems oi (NOLOCK)
     JOIN Orders o (NOLOCK) ON o.OrderID = oi.OrderID
     JOIN Products p (NOLOCK) ON p.ProductID = oi.ProductID
     JOIN Counties co (NOLOCK) ON co.FIPS = o.FIPS
     JOIN Milestones m (NOLOCK) ON m.MilestoneID = oi.LastMilestoneID
     LEFT JOIN PropertyTypes pt (NOLOCK) ON pt.PropertyTypeID = o.PropertyTypeID
     LEFT JOIN OrderContacts oc (NOLOCK) ON oc.OrderID = oi.OrderID AND oc.ContactTypeID = 11
     WHERE oi.QueueID > 0
     and (convert (varchar(10), oi.RequiredByDate, 110)) <= (convert (varchar(10), GETDATE(), 110))  
     GROUP BY 
     (convert (varchar(10), oi.OrderDate, 110)), 
     o.LoanNumber AS AppNo, oc.LastName, oc.FirstName, p.Abbreviation ,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))),
     o.City, o.State, co.Name, m.Description,
     (convert (varchar(10), oi.RequiredByDate, 110)) ,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END
    
     

    Forgot to add the group by - hard to test anything in notepad with no database.

    This should be close


    Chuck

    Monday, April 16, 2012 12:57 AM
  • Thanks again, Im still getting an error at this section i tried to figure it out but no luck. I think its just a ( or ) somewhere but not sure where.

    Msg 156, Level 15, State 1, Line 28
    Incorrect syntax near the keyword 'AS'.

    GROUP BY 
     (convert (varchar(10), oi.OrderDate, 110)),
     o.LoanNumberAS AppNo, oc.LastName, oc.FirstName, p.Abbreviation ,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))),
     o.City, o.State, co.Name, m.Description,
     (convert (varchar(10), oi.RequiredByDate, 110)) ,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END

    Monday, April 16, 2012 1:03 AM
  • added this but no luck

    GROUP BY 
     (convert (varchar(10), oi.OrderDate, 110)) AS RequestDate,.....

    Monday, April 16, 2012 1:05 AM
  • Change the group by to this:  Missed removing an AS

    GROUP BY 
     (convert (varchar(10), oi.OrderDate, 110)), 
     o.LoanNumber, 
    oc.LastName, 
    oc.FirstName, 
    p.Abbreviation ,
     CONVERT(int, (DATEDIFF(day, (dbo.TruncateDate(oi.RequiredByDate)), GETDATE()))),
     o.City, o.State, co.Name, m.Description,
     (convert (varchar(10), oi.RequiredByDate, 110)) ,
     CASE
     WHEN dbo.TruncateDate(GETDATE()) = dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Today'
     WHEN GETDATE() > oi.RequiredByDate THEN 'OVERDUE'
     ELSE 'On Time'
     END
    


    Chuck

    Monday, April 16, 2012 1:12 AM
  • thanks i can make a pivot out of this or something, not the exact result i was looking for but it will give me more than i had before!
    Monday, April 16, 2012 1:22 AM