Get total time different between multiple time ranges-CodePudding

I have a table containing an id, an action, and a datetime field. That looks like this:

ID	ACTION	DATETIME
1	start	2022-01-01 00:00:00
1	end	2022-01-01 00:04:30
1	start	2022-01-01 00:08:00
1	end	2022-01-01 00:08:30
2	start	2022-01-01 00:00:00
2	end	2022-01-01 00:01:30
2	start	2022-01-01 00:02:00
2	end	2022-01-01 00:02:45

I am trying to figure out a way to add up all of the times between each start/end pair. This is something I could figure out in Python or JS, but I'm having trouble figuring out the logic in SQL since there can be more than one start/end instance per ID.

Anyone know of a way to iterate through each ID group, ordered by datetime, and get the total time between each range using DATEDIFF? For the table above, the end result should look like this:

ID	TOTAL_TIME
1	00:05:00
2	00:02:15

Trying to avoid using UDFs.

CodePudding user response：

This is a gaps and islands problem which we can solve using the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATETIME) rn1,
              ROW_NUMBER() OVER (PARTITION BY ID, ACTION ORDER BY DATETIME) rn2
    FROM yourTable
),
cte2 AS (
    SELECT ID,
           DATEDIFF(minute,
                    MAX(CASE WHEN ACTION = 'start' THEN DATETIME END),
                    MAX(CASE WHEN ACTION = 'end'   THEN DATETIME END)) AS diff
    FROM cte
    GROUP BY id, rn1 - rn2
)

SELECT ID, SUM(diff) AS TOTAL_TIME_IN_MINUTES
FROM cte2
GROUP BY ID
ORDER BY ID;