0

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

0

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)<60*24 THEN 1 ELSE 0 END)) AS below24Hrs,
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)>=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.

  • ITs working, i will just simplify the rest of the query as you suggested. – Arvin Aug 26 '14 at 13:02
  • Can I put another Criteria in the SUM Case? smimilar to this --- SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)<60*24 AND RG.RegionName = 'CENTRAL' THEN 1 ELSE 0 END)) AS below24Hrs, – Arvin Aug 26 '14 at 13:41
  • Yep, just like that. – Daniel Hutmacher Aug 26 '14 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.