Forum Discussion
SQL Help : Time difference for events within a specific group that resets based on an anchor event
To solve the challenge of calculating the time differences based on the anchor event and subsequent events in the group while differentiating the durations from the anchor event, you can refine the SQL query by adding an explicit calculation for the anchor-to-current-event duration.
WITH GroupedEvents AS (
SELECT
EventType,
EventDatetime,
SUM(CASE WHEN EventType = 'A' THEN 1 ELSE 0 END)
OVER (ORDER BY EventDatetime) AS GroupID
FROM events
),
AnchorTimes AS (
SELECT
GroupID,
MIN(CASE WHEN EventType = 'A' THEN EventDatetime END) AS AnchorTime
FROM GroupedEvents
GROUP BY GroupID
),
TimeDifference AS (
SELECT
g.EventType,
g.EventDatetime,
g.GroupID,
a.AnchorTime,
DATEDIFF(SECOND,
LAG(g.EventDatetime) OVER (PARTITION BY g.GroupID ORDER BY g.EventDatetime),
g.EventDatetime) AS DurationFromPrevious,
DATEDIFF(SECOND, a.AnchorTime, g.EventDatetime) AS DurationFromAnchor
FROM GroupedEvents g
JOIN AnchorTimes a
ON g.GroupID = a.GroupID
)
SELECT
EventType,
EventDatetime,
GroupID,
CASE
WHEN EventType = 'A' THEN 0
ELSE DurationFromPrevious
END AS Duration_Previous_Event,
CASE
WHEN EventType = 'A' THEN 0
ELSE DurationFromAnchor
END AS Duration_From_Anchor
FROM TimeDifference
ORDER BY GroupID, EventDatetime;The query will produce:
Duration_Previous_Event: Time difference between the current event and the previous event in the group.
Duration_From_Anchor: Time difference between the current event and the anchor event (A) in the group.
Note:
This solution handles group resets based on the anchor event.
You can adjust the DATEDIFF function or time unit (e.g., SECOND, MINUTE) as per your requirements.
Ensure the data in your EventDatetime column is sorted correctly and contains no nulls.
You can use recursive CTE to resolve the question. Get rows with value 'A' in Event Type as anchor query, and attach next Event Datetime with value 'A' as partition boundary.
Recursive gets rows that match next Event Type value in each partitions.
Code is here:
--Create Test table include 2 columns
CREATE TABLE #Events
(
[Event Type] CHAR(1),
[Event Datetime] DATETIME,
)
GO
--Fill test data
INSERT INTO #Events
VALUES
('A', '2024-11-18T00:00:25.000Z')
,('B', '2024-11-18T00:00:35.272Z')
,('C', '2024-11-18T00:00:35.272Z')
,('D', '2024-11-18T00:00:36.000Z')
,('E', '2024-11-18T00:00:39.406Z')
,('F', '2024-11-18T00:00:39.406Z')
,('A', '2024-11-18T00:02:33.000Z')
,('B', '2024-11-18T00:02:42.272Z')
,('C', '2024-11-18T00:02:42.272Z')
,('D', '2024-11-18T00:03:44.000Z')
,('E', '2024-11-18T00:03:46.406Z')
,('F', '2024-11-18T00:03:46.406Z')
,('A', '2024-11-18T00:04:41.000Z')
,('B', '2024-11-18T00:04:50.352Z')
,('C', '2024-11-18T00:04:50.352Z')
,('D', '2024-11-18T00:04:51.000Z')
,('E', '2024-11-18T00:04:54.959Z')
,('F', '2024-11-18T00:04:54.959Z')
,('A', '2024-11-18T00:06:49.000Z')
,('B', '2024-11-18T00:06:58.172Z')
,('C', '2024-11-18T00:06:58.172Z')
,('D', '2024-11-18T00:07:59.000Z')
,('E', '2024-11-18T00:08:02.861Z')
,('F', '2024-11-18T00:08:02.861Z')
GO
--Use recursive CTE
WITH C --Create CTE with some addition columns
AS
( --anchor query that contain [Anchor Type]: A; [Anchor Time]: lower bound to split partition; [Next Anchor]: upper bound to split partition;
SELECT [Event Type], [Event Datetime]
, CAST(0 AS INT) AS [Duration]
, [Event Type] AS [Anchor Type]
, [Event Datetime] AS [Anchor Time]
, LEAD([Event Datetime]
, 1
, GETDATE()) OVER (ORDER BY [Event Datetime]) AS [Next Anchor] --Get next [Event Datetime] as upper bound of this partition, get current time as upper bound of last partition.
FROM #Events WHERE [Event Type]='A'
UNION ALL
--recursive query that calc [Duration]
SELECT E.[Event Type], E.[Event Datetime]
, DATEDIFF(MILLISECOND, C.[Anchor Time], E.[Event Datetime]) AS [Duration] --Use SECOND or other unit of measure to calculate the difference.
, C.[Anchor Type]
, C.[Anchor Time]
, C.[Next Anchor]
FROM #Events E
INNER JOIN C
ON
E.[Event Type] = CHAR(ASCII(C.[Event Type])+1) --Get next row by [Event Type]
WHERE E.[Event Datetime]>=C.[Anchor Time] AND E.[Event Datetime]<C.[Next Anchor] --limit rows by bounds defined by anchor query - Make partition
)
SELECT [Event Type], [Event Datetime], [Duration] --Columns will be shown
FROM C
ORDER BY [Event Datetime]
GO
DROP TABLE IF EXISTS #Events
GO