I have the following table

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

CodePudding user response：

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]

fiddle