Calculating Difference between Multiple Dates for a Given Condition in HH:MM:SS

  • We have the following requirement:-

    Event_Time                                                action_id                     session_id

    2025-06-06 09:00:00.000                          DBAS                                  55

    2025-06-06 09:02:24.256                           BCM                                     55

    2025-06-06 09:06:48.512                           BCM                                     55

    2025-06-06 12:45:47.479                          DBAS                                     55

    2025-06-06 12:45:53.687                          BCM                                       55

    I want to calculate the total time in HH:MM:SS where each action_id commences with 'DBAS' for a particular session_id; in this case for session_id=55; we should get 2 rows as o/p for total time in HH:MM:SS  because of the following conditions.

    1. First event started at 2025-06-06 09:00:00.000  for action_id='DBAS' and session_id=55; however this event ended at 2025-06-06 09:06:48.512
    2. Second event started at 2025-06-06 12:45:47.479 for action_id='DBAS' and session_id=55; however this event ended at 2025-06-06 12:45:53.687

    Please help.

     

     

     

  • How do you know from the data when a DBAS event ends? Just prior to the next DBAS event?

  • Even ignoring connection pooling, the question is not clear. It seems to have something to do with islands and gaps. Here is an example which might help. Good luck.

    /* *** Test Data which you should provide *** */
    CREATE TABLE #t
    (
    Event_Time datetime2(3) NOT NULL
    ,action_id varchar(10) NOT NULL
    ,[session_id] smallint NOT NULL
    ,PRIMARY KEY ([session_id], Event_Time, action_id)
    );
    INSERT INTO #t
    VALUES ('2025-06-06 09:00:00.000', 'DBAS', 55)
    ,('2025-06-06 09:02:24.256', 'BCM', 55)
    ,('2025-06-06 09:06:48.512', 'BCM', 55)
    ,('2025-06-06 12:45:47.479', 'DBAS', 55)
    ,('2025-06-06 12:45:53.687', 'BCM', 55);
    /* *** End Test Data *** */

    WITH Boundaries
    AS
    (
    SELECT Event_Time, action_id, [session_id]
    ,IIF(action_id = 'DBAS', 1, 0) AS Boundary
    FROM #t
    )
    ,SessionGroups
    AS
    (
    SELECT Event_Time, action_id, [session_id]
    ,SUM(Boundary) OVER (PARTITION BY [session_id] ORDER BY Event_Time) AS Grp
    FROM Boundaries
    )
    ,EventRanges
    AS
    (
    SELECT [session_id]
    ,MIN(Event_Time) AS Event_Start
    ,MAX(Event_Time) AS Event_End
    FROM SessionGroups
    GROUP BY [session_id], Grp
    )
    SELECT E.[session_id], E.Event_Start, E.Event_End
    ,CASE
    WHEN W.WholeDays > 0
    THEN
    CONCAT
    (
    CAST(W.WholeDays * 24 + CAST(LEFT(DurationTime, 2) AS int) AS varchar(20))
    , SUBSTRING(DurationTime, 3, 20)
    )
    ELSE W.DurationTime
    END AS Duration
    FROM EventRanges E
    CROSS APPLY
    (
    VALUES
    (
    DATEADD(ms, DATEDIFF(ms, Event_Start, Event_End), CAST('1900' AS datetime2(3)))
    )
    ) I (Interval)
    CROSS APPLY
    (
    VALUES
    (
    DATEDIFF(day, CAST('1900' AS datetime2(3)), I.Interval)
    ,CONVERT(varchar(20), I.Interval, 14)
    )
    ) W (WholeDays, DurationTime);

  • Thank you; i tested it looks Great. How do I add all the values in the column Duration considering it to be in format HH:MM:SS:MS; please help on that.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply