June 6, 2025 at 5:53 pm
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.
Please help.
June 7, 2025 at 3:58 pm
How do you know from the data when a DBAS event ends? Just prior to the next DBAS event?
June 7, 2025 at 9:38 pm
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);
June 9, 2025 at 5:37 pm
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