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

Question

Post reply

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

satnam.singh 44358

Ten Centuries

Points: 1305
More actions
June 6, 2025 at 5:53 pm

#4603727
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.

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

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

pietlinden SSC Guru Points: 63591 More actions · Answer 1

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

Ken McKelvey SSCoach Points: 18927 More actions · Answer 2

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);

satnam.singh 44358 Ten Centuries Points: 1305 More actions · Answer 3

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.

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

Cookies on SQLServerCentral