SQL Help : Time difference for events within a specific group that resets based on an anchor event

Hi All,

Need help with an SQL query to find the time difference between events that happen in sequence, where an anchor event resets the calculation. Below is the sample data and the expected output.

thanks

MSSQL Server

SQL

Mks_1973
Nov 20, 2024

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.

Mks_1973
Iron Contributor
Nov 20, 2024
To solve the problem of calculating the time difference between sequential events within a group that resets based on an anchor event, you can use a combination of SQL's window functions and conditional logic.

Assumption:
Your table is named events and has the following columns:
EventType (e.g., A, B, C)
EventDatetime (timestamp)

Assign Groups Based on the Anchor Event:
(Use the CASE and SUM functions to create a grouping column (GroupID) that resets every time an anchor event (A) occurs)

WITH GroupedEvents AS (
SELECT
*,
SUM(CASE WHEN EventType = 'A' THEN 1 ELSE 0 END)
OVER (ORDER BY EventDatetime) AS GroupID
FROM events
)

Then Calculate the Time Difference Within Each Group:
(Use the LAG function to calculate the difference in time between the current and the previous event within each group)

, TimeDifference AS (
SELECT
EventType,
EventDatetime,
GroupID,
DATEDIFF(SECOND,
LAG(EventDatetime) OVER (PARTITION BY GroupID ORDER BY EventDatetime),
EventDatetime) AS Duration
FROM GroupedEvents
)

Handle the Anchor Event:
(Set the duration of the anchor event (A) to 0 since it resets the group)

SELECT
EventType,
EventDatetime,
GroupID,
CASE
WHEN EventType = 'A' THEN 0
ELSE Duration
END AS Duration
FROM TimeDifference
ORDER BY EventDatetime;

Expected output would be:
Anchor event (A) resets the duration.
Other events calculate the time difference from the previous event within the same group.
- suki
  Copper Contributor
  Nov 20, 2024
  Hi Mks_1973
  thanks that was helpful but place where i am struggling is to get difference based on Anchor item and items within the group ,is there a way that we can achieve that ?
  - Mks_1973
    Iron Contributor
    Nov 20, 2024
    
    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.

rodgerkong

Iron Contributor

Nov 20, 2024

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

Forum Discussion

SQL Help : Time difference for events within a specific group that resets based on an anchor event

Share

Resources