If you want to calcualte the time difference between the first record and the next record - we can use the following SQL Query.
EX : Data
In the above sample to calculate the Time difference between the First incident's closed Time and the next incident's Start Time (we can call it as a time between incidents)
SQL :
WITH CTE
as (
SELECT IncidentNumber, IncidentOpenDate, IncidentClosedDate,
( SELECT MAX(T1.IncidentClosedDate)
FROM Trn_Incidents T1
WHERE T1.IncidentClosedDate < T2.IncidentOpenDate
) AS Previous_IncidentEndTime
FROM Trn_Incidents T2
)
SELECT IncidentNumber, IncidentOpenDate, IncidentClosedDate , DATEDIFF(MI,Previous_IncidentEndTime,IncidentOpenDate) AS IdleTime
FROM CTE
The Result would be :
Now we got the idle time between the incident sequence..!!
