-3

I need to build a SQL query in which I can get time spent on multiple statuses (onHold,Waiting for customer,Resolved,Closed), so basically I do not want to include time spent on this statues and table looks like as below

enter image description here

So I need a query in which I can get actual time spent on ticket or time spent on status which I have mentioned so far I have tried below solutions and tried Cross APPLY but seems all did not help me as expected.

Tried below query so far and that gives me correct time spent on first status on-hold not after that:

SELECT t1.TICKETNUMBER,SUM(DATEDIFF(MINUTE,TICKETTIME,CloseTime)) as TotalMinutes
FROM [Admin].[TbtrnTicketHistory] t1
CROSS APPLY(SELECT TOP 1 TICKETTIME as CloseTime FROM [Admin].[TbtrnTicketHistory] t2 WHERE t1.TICKETNUMBER = t2.TICKETNUMBER and t2.TICKETHISTORYID > t1.TICKETHISTORYID ORDER BY t2.TICKETTIME) as t2
WHERE t1.CURRENTSTATUS_ANALYST not in('On-Hold','Waiting For Customer','Resolved','Closed') and t1.ticketnumber = '211135'
GROUP BY t1.TICKETNUMBER;

calculate difference between two times in two rows in sql

Calculate Time Difference Between Two Consecutive Rows

Dale K
  • 25,246
  • 15
  • 42
  • 71
Wit Wikky
  • 1,542
  • 1
  • 14
  • 28
  • 3
    Most people here want sample table data _and the expected result_ as formatted text, not as images. Also show us your current query attempt. – jarlh Nov 03 '21 at 07:41
  • updated to the question – Wit Wikky Nov 03 '21 at 07:57
  • Are you sure you're using MySQL? The query looks more like MS SQL Server code. – jarlh Nov 03 '21 at 07:57
  • yes sql server 2012 – Wit Wikky Nov 03 '21 at 08:02
  • Then you can switch the tag to . – jarlh Nov 03 '21 at 08:08
  • You state you want "time spent on ticket or on status" (without defining what that means) while your query filters out multiple values for status. And you group by ticketnumber which means you want one row per ticket - so how do YOU calculate it manually given a specific set of sample data? It sounds like you have contradictory goals. – SMor Nov 03 '21 at 10:10
  • So basically i want to exclude time spent on this statues 'On-Hold','Waiting For Customer','Resolved','Closed' – Wit Wikky Nov 03 '21 at 10:24

1 Answers1

0

with SQL Server you can use those very usefull windowed functions LEAD and FIRST_VALUE :

select *
    ,[duration(sec)] = DATEDIFF(SECOND
                                ,ticketTime
                                ,LEAD(ticketTime,1,ticketTime)over(partition by ticketNumber order by ticketTime)
                                )

    ,[cumulative duration(sec)] = DATEDIFF( SECOND
                            , FIRST_VALUE(ticketTime)over(partition by ticketNumber order by ticketTime)
                            , ticketTime)
from (values
     (1,cast('20211101 10:00:01' as datetime))
    ,(1,'20211101 10:00:33')
    ,(1,'20211101 10:01:59')
 )T(ticketNumber,ticketTime)
ticketNumber ticketTime duration(sec) cumulative duration(sec)
1 2021-11-01 10:00:01.000 32 0
1 2021-11-01 10:00:33.000 86 32
1 2021-11-01 10:01:59.000 0 118
jjdesign
  • 344
  • 2
  • 8