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))

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

added this but no luck

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

Answer 6

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

Answer 7

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!