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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy