Difference of two date time from the same column base on condition in SQL Server

Question

How to calculate time difference of the two same date in the same column with base on other column condition and display new extra column with difference values?

Table datetime_log:

id  logdatetime                EventType    
----------------------------------------------
1 2022-03-10 01:04:38.913   Started
2 2022-03-10 01:06:38.913   Done

I need my output to be like :

id  logdatetime                EventType    duration  
----------------------------------------------
1 2022-03-10 01:04:38.913   Started         0
2 2022-03-10 01:06:38.913   Done          00:02:38.913 

Answer 1

A CROSS APPLY or OUTER APPLY can be used to search for the preceding "Started" event type. Subtracting the start date from the current log date will give you another DATETIME value containing the duration, but with a '1900-01-01' base date. If your durations never exceed 24 hours, you can format as a string and strip off the date part.

Try something like:

SELECT L.*,
    Elapsed = STUFF(CONVERT(VARCHAR(50), L.logdatetime - S.Started, 121), 1, 11, '')
FROM datetime_log L
CROSS APPLY (
    SELECT TOP 1 Started = L2.logdatetime
    FROM datetime_log L2
    WHERE L2.id <= L.id  -- Use logdatetime
    AND L2.EventType = 'Started'
    ORDER BY L2.id DESC  -- Use logdatetime
) S
ORDER BY L.id

Format 121 yields a "yyyy-mm-dd hh:mm:ss.fff" result. The STUFF() function strips off the date part (first 11 characters).

If logdatetime is not guaranteed to be ascending by id, you will need to change the id references to logdatetime where noted above.

If there is a chance that the data does not start with a "Started" event, change CROSS APPLY to OUTER APPLY, which is similar to a LEFT JOIN.

If durations might exceed 24 hours, you will need to separately extract a days value from the duration using something like DATEDIFF(day, '1900-01-01', L.logdatetime - S.Started) and prepend it to the result.

See this db<>fiddle for a demo.