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.