Skip to content

How to Calculate the Time Between Two Rows in SQL Server

I had an interesting problem to solve: How long did a task take to complete? The challenge was that our task history table contains one row for the start and another one for the end of a task:

Id TaskId Action Timestamp
1 100 Start 2022-08-01 08:00:00.000
2 101 Start 2022-08-02 07:30:00.000
3 100 Stop 2022-08-03 17:00:00.000
4 102 Start 2022-08-04 08:00:00.000
5 102 Stop 2022-08-04 17:00:00.000
6 101 Stop 2022-08-05 18:00:00.000

I needed to combine the two matching rows to create an output like this:

TaskId Duration
100 3
101 4
102 1

OUTER APPLY

In SQL Server we can use the OUTER APPLY operator to combine a table with itself. It is basically a LEFT OUTER JOIN, but with a bit more functionality.

This statement takes the start times of the tasks and combines them with the end times of the same task, calculates the difference in days and adds 1 to catch the partial days:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT
    [TaskId], 
    CONVERT(DATE, taskStart.[Timestamp]) AS 'Start',
    CONVERT(DATE, taskEnd.[Timestamp]) AS 'End',
    DATEDIFF(day, taskStart.[Timestamp], taskEnd.[Timestamp]) + 1 as 'Duration'
FROM #TaskDuration taskStart
OUTER APPLY (
    SELECT TOP(1)
        taskEnd.[Timestamp]
    FROM #TaskDuration taskEnd
    WHERE 
        taskEnd.[Timestamp] > taskStart.[Timestamp]
        AND taskEnd.[Action] = 'Stop'
        AND taskEnd.TaskId = taskStart.TaskId
    ORDER BY [Timestamp]
    ) taskEnd
WHERE
    taskStart.Action = 'Start'

When I run this query against the example data from above, it produces the result I need:

TaskId Start End Duration
100 2022-08-01 2022-08-03 3
101 2022-08-02 2022-08-05 4
102 2022-08-04 2022-08-04 1

This query works even when a task needs multiple runs, because the ORDER BY [Timestamp] gives us the end times in ascending order (and so the SELECT TOP(1) finds the closest end time to the specific start time of the task).

Conclusion

SQL Server saved me a lot of programming time with its OUTER APPLY function. I got the result I needed in a few minutes and the query runs so fast that I can run it whenever I need an update. There are other solutions, but I liked this one and did not bother to try something else.