I feel like this is a common issue but I'm having problems finding good examples on how to accomplish this task.
This is cases where date ranges should be combined into a single start date and a single end date when date ranges overlap.
-- Example Data
declare @t table (Name varchar(100), Datetime_Start datetime, Datetime_End datetime);
insert into @t values
('A' , '2023-01-02 00:00' , '2023-03-28 00:10'),
('A' , '2023-05-14 23:50' , '2023-05-29 23:50'),
('B' , '2023-05-18 00:00' , '2023-05-18 04:00'),
('B' , '2023-05-18 02:00' , '2023-05-18 03:00'),
('C' , '2023-01-02 00:00' , '2023-01-17 15:50'),
('C' , '2023-01-14 03:50' , '2023-01-28 15:50'),
('C' , '2023-01-25 03:50' , '2023-02-05 15:50');
What I want in the results is:
'A' '2023-01-02 00:00' : '2023-03-28 00:10'
'A' '2023-05-14 23:50' : '2023-05-29 23:50'
'B' '2023-05-18 00:00' : '2023-05-18 03:00'
'C' '2023-01-02 00:00' : '2023-02-05 15:50'
Not really sure how to accomplish this one.
This is based on code by Itzik Ben-Gan. It uses a cross apply and a sum windowed function to increment the open interval count for start times and decrement it for end times. It then looks for records where the previous open interval count was zero to determine interval groups, and then finds the min and max (start and end) times for those interval groups.
WITH Interval_Counts AS
(
SELECT *, SUM(d.open_interval_increment) OVER(PARTITION BY [t].[Name] ORDER BY d.dt_val ROWS UNBOUNDED PRECEDING) AS open_interval_cnt
FROM @t AS t
CROSS APPLY ( VALUES(t.Datetime_Start, 1), (t.Datetime_End, -1) ) d(dt_val, open_interval_increment)
)
, Interval_Groups AS
(
SELECT *
, COUNT(CASE WHEN ic.open_interval_cnt = 0 THEN 1 END) OVER(PARTITION BY [ic].[Name] ORDER BY ic.dt_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Interval_Grp
FROM Interval_Counts AS ic
)
SELECT ig.[Name], MIN(ig.dt_val) AS Datetime_Start, MAX(ig.dt_val) AS Datetime_End
FROM Interval_Groups AS ig
GROUP BY ig.[Name], ig.Interval_Grp
ORDER BY ig.[Name], ig.Interval_Grp
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2023 at 7:22 am
This was removed by the editor as SPAM
August 1, 2023 at 12:24 pm
This was removed by the editor as SPAM
August 1, 2023 at 6:45 pm
Many thanks, that helps!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply