Search code examples
sqlsql-servert-sqlsql-server-2016

Calculate time difference by filtering and combining related entries from same table

I have the following table

TicketID Operator Datestamp Remarks
1 p1 July 20, 2022, 10:30 PM Changed from State A to B
1 p1 July 20, 2022, 11:30 PM Changed from State B to C
1 p2 July 21, 2022, 10:01 PM Changed from State D to B
1 p3 July 21, 2022, 11:41 PM Changed from State B to A
2 p1 November 13, 2022, 11:01 PM Changed from State C to B
3 p5 November 13, 2022, 09:10 AM Changed from State A to B
3 p1 November 13, 2022, 11:10 AM Changed from State B to C
3 p1 November 13, 2022, 11:41 PM Changed from State C to B

I need to find out the duration tickets(identified by TicketID) have spent in State B To clarify further referencing the table above Ticket 1 has spent from July 20, 2022, 10:30 PM to 11:30 PM (1hrs) and July 21, 2022, 10:01 PM to 11:41 PM(1hr40min) in state B making a total of (2hrs40min).

Similarly, Ticket 2 has just one state change to B and there is no entry for a state change from B, hence we assume it is still in State B and the duration becomes CurrentTime-November 13, 2022, 11:01 PM.

I'm having a hard time figuring out how to achieve this in a TSQL View. Any help is highly appreciated. Assuming current time is November 13, 2022, 11:51 PM The final view output is supposed to be something like below

TicketID Duration(in minutes)
1 160
2 50
3 130
Solution

  • Ok, with the new changed data, it can be done so.

    First determinec what is to and FROM then, get the LEAD of dateime

    Finally sum the difference in minutes up

    You still should always provide dates as yyyy-mm-dd hh:MM:ss

    WITH CTE as (SELECT
[TicketID], [Operator], [Datestamp],
CASE WHEN [Remarks] LIKE '%to B%' THen 1
WHEN [Remarks] LIKE '%B to%' THen 2
ELSE 0 END flg
FROM tab1
),
CTE2 AS (SELECT
 [TicketID],flg,[Datestamp], LEAD([Datestamp]) OVER(PARTITION BY [TicketID] ORDER BY [Datestamp] ) date2  
FROM CTE)
SELECT
[TicketID],SUM(ABS(datediff(minute,[Datestamp],COALESCE(date2, getdate())))) as duration
FROM CTE2
WHERE flg = 1
GROUP BY [TicketID]
    TicketID duration
    1 160
    2 450
    3 610

    fiddle