Count Function with Aggregate function inside the query Hi my actual code is below:

Select M.TicketID,M.CreatedMoment, Max(L.StatusChangeMoment)AS StatusChangeTime,
Elapsed_time_in_Hours_Minutes = CONVERT(NUMERIC(18,2),DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment))/60+(DATEDIFF(minute, M.createdmoment, MAX(L.statuschangemoment)) % 60/100.0))
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID 
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc


And the Partial Results is this:

TicketID    CreatedMoment   StatusChangeTime    Elapsed_time_in_Hours_Minutes
201408231   8/23/14 8:05 AM 8/25/14 11:47 AM    51.42
2014082310  8/23/14 8:19 AM 8/23/14 12:43 PM    4.24
20140823100 8/23/14 8:38 AM 8/24/14 11:15 AM    26.37
20140823101 8/23/14 8:38 AM 8/23/14 11:58 AM    3.2
20140823102 8/23/14 8:38 AM 8/24/14 10:33 AM    25.55


Basically the statuschangetime came from aggregate function, and the last column is the difference of the 2nd and 3rd column.

I want to modify the query so the results will look like this:

Date          below24Hrs    above24hours
2014-8-23        2              3


My problem is i'm getting error when running this code:

Select
[below24hrs] = COUNT (Case WHEN (CONVERT(NUMERIC(18,2),DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment))/60+(DATEDIFF(minute, TM.createdmoment, MAX(LG.statuschangemoment)) % 60/100.0))) &lt; 24 THEN 1 END)
From XX_MASTER_TICKETS AS M Left Join XX_DETAIL_TICKET_STATUS_LOG AS L
On M.RowID = L.TicketRowID 
Where M.CreatedMoment between '08-23-2014' And '08-26-2014'

Group by M.TicketID,M.CreatedMoment
Order by M.TicketID asc


It says cannot count with the MAX aggregrate function inside the query.
Put the first select in a subquery or a common table expression (shown below)

WITH cte (TicketID, CreatedMoment, StatusChangeTime)
AS (
    SELECT M.TicketID,
           M.CreatedMoment,
           MAX(L.StatusChangeMoment) AS StatusChangeTime
    FROM XX_MASTER_TICKETS AS M
    LEFT JOIN XX_DETAIL_TICKET_STATUS_LOG AS L ON M.RowID = L.TicketRowID
    WHERE M.CreatedMoment BETWEEN {d '2014-08-23'} AND {d '2014-08-26'}
    GROUP BY M.TicketID,
             M.CreatedMoment
   )

SELECT CAST(CreatedMoment AS date) AS [Date],
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)&lt;60*24 THEN 1 ELSE 0 END)) AS below24Hrs,
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)&gt;=60*24 THEN 1 ELSE 0 END)) AS above24Hrs
FROM cte
GROUP BY CAST(CreatedMoment AS date)
ORDER BY 1;


The common table expression returns a list of TicketID, CreatedMoment and MAX(StatusChangeMoment). Using this result set, we can apply a second aggregate in the SELECT statement below. What I'm doing there is a conditional SUM(). I'm adding 1 for each row that meets the criteria and 0 for each row that doesn't.

NB: I've simplified your criteria a bit, I hope it's equivalent to what you're looking for.