Merge overlapping time frames (min and max) into one and blanket assign to relevant user if detection time criteria is matched

Question

I have a database that records user access. User access is requested and users are given a two hour window to access their space. At the moment, users can request multiple requests, which sometimes overlap. We also record the time that they access the space. If a user's has more than one request and it overlaps with each other, I need to merge these into the earliest time and the latest time of the last time window.

Here is some code to create some sample data to illustrate my problem. I also attach an image of what the desire result must be: desired outcome

CREATE TABLE #temp (
    Ticketnumber int,
    UserID varchar(20),
    DetectionTime datetime,
    TimeWindowStart datetime,
    TimeWindowEnd datetime)

insert into #temp (Ticketnumber ,UserID, DetectionTime, TimeWindowStart, TimeWindowEnd)
VALUES
('567270', 'mark', '2022-12-15 08:42:28.000', '2022-12-15 08:38:46.000', '2022-12-15 10:53:56.000'),
('567270', 'mark', '2022-12-15 09:34:52.000', '2022-12-15 08:38:46.000', '2022-12-15 10:53:56.000'),
('578596', 'mark', '2022-12-15 09:29:37.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('578596', 'mark', '2022-12-15 09:35:37.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('578596', 'mark', '2022-12-15 09:34:52.000', '2022-12-15 09:09:28.000', '2022-12-15 11:24:33.000'),
('045611', 'mark', '2023-02-01 16:13:42.000', '2023-02-01 15:56:41.000', '2023-02-01 18:11:43.000'),
('626948', 'anna', '2022-12-20 13:18:20.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('626948', 'anna', '2022-12-20 12:06:40.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('626948', 'anna', '2022-12-20 13:15:39.000', '2022-12-20 11:33:25.000', '2022-12-20 13:48:32.000'),
('627361', 'anna', '2022-12-20 13:18:20.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 12:17:43.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 13:07:31.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 12:06:40.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000'),
('627361', 'anna', '2022-12-20 13:15:39.000', '2022-12-20 11:33:31.000', '2022-12-20 13:48:34.000')

SELECT Ticketnumber, Userid, [detectiontime], TimeWindowStart,     TimeWindowEnd,
     Lag([timewindowstart], 1) OVER(
       ORDER BY userid, TimeWindowStart ASC) AS LagStartDate,
     Lead([TimewindowEnd], 1) OVER(
       ORDER BY userid, [TimewindowEnd] ASC) AS LagEndDate
from #temp t2
order by userid, TimeWindowStart

I have tried lag and lead functions but without achieving the desired outcome. Can someone please assist?

Answer 1

I have create a query below to know which 2 tickets are overlapped and its combined TimeWindowStart and TimeWindowEnd. Its not exactly in the format you want but you can take it from here.

In your table above, even Anna has overlap between ticket 627361 and 626948.

Limitation: What if more than 2 tickets are overlapped.

WITH cte AS ( SELECT Ticketnumber, Userid, [detectiontime], TimeWindowStart,     TimeWindowEnd,
     Lag([timewindowstart], 1) OVER(
       ORDER BY userid, TimeWindowStart ASC) AS LagStartDate,
     Lead([TimewindowEnd], 1) OVER(
       ORDER BY userid, [TimewindowEnd] ASC) AS LagEndDate,
      Lag([userid], 1) OVER(
           ORDER BY userid, TimeWindowStart ASC) AS Laguserid,
      Lag([ticketnumber], 1) OVER(
           ORDER BY userid, TimeWindowStart ASC) AS Lagticketnumber
    from #temp t2

) 
select ticketnumber,lagticketnumber, userid
    , LagStartDate as TimeWindowStart
    , LagEndDate as TimeWindowEnd, 
    IIF(cte.userid = cte.laguserid 
       and cte.TimeWindowStart <> cte.LagStartDate
       and TimeWindowStart between LagStartDate and LagEndDate
       , 1, 0) is_overlap 
from cte 
where IIF(cte.userid = cte.laguserid 
   and cte.TimeWindowStart <> cte.LagStartDate
   and TimeWindowStart between LagStartDate and LagEndDate
   , 1, 0) = 1
order by userid, TimeWindowStart;

OUTPUT: