Get the min start and max end when the datetime overlap is more than 1 day

Transact-SQL
1

Hi , I have found this thread which is working fine to get the min start datetime and max datetime when there is an overlap between the date time on a given date..but i wanted to use the same and extend it to get the min start datetime and max end datetime when the overlap is not on the same day

for example the last record for Raj should be as it is meaning get the start datetime and end datetime as it since this is not an overlap - but for the record that is above the last record

('Raj' ,'04-01-2020 11:00','04-02-2020 02:30') - this started at 11am and ended at 2.30pm
('Raj' ,'04-01-2020 12:00','04-02-2020 03:30') - this started 12pm and ended at 330

this is an example of overlap where it is not the same day - so here we need just what we did when it was same day - that is we picked the min of start datetime and max of end datetime for that name

could some please help - really appreciate your help

drop table #TestTable

SELECT Name = CONVERT(VARCHAR(10),v.Name)
,StartDT = CONVERT(DATETIME,v.StartDT)
,EndDT = CONVERT(DATETIME,v.EndDT)
INTO #TestTable
FROM (VALUES
('Aman','01-02-2020 04:30','01-02-2020 06:30')
,('Aman','01-02-2020 08:30','01-02-2020 09:30')
,('Aman','01-02-2020 04:40','01-02-2020 05:30')
,('Aman','01-02-2020 04:55','01-02-2020 07:30')
,('Aman','01-02-2020 14:55','01-02-2020 18:30')
,('Aman','01-02-2020 13:40','01-02-2020 15:30')
,('Ram' ,'01-02-2020 04:40','01-02-2020 05:30')
,('Ram' ,'01-02-2020 04:40','01-02-2020 05:30')
,('Raj' ,'04-01-2020 11:00','04-02-2020 02:30')
,('Raj' ,'04-01-2020 12:00','04-02-2020 03:30')
,('Raj' ,'04-01-2020 10:00','04-02-2020 10:30')

    )v(Name,StartDT,EndDT)

;

WITH
C1 AS
(
SELECT Name, TS = StartDT, TYPE = +1, E = NULL, S = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY StartDT)
FROM #TestTable
UNION ALL
SELECT Name, TS = EndDT, TYPE = -1, E = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY EndDT), S = NULL
FROM #TestTable
)
,C2 AS
(
SELECT C1.*,
SE = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ts, type DESC)
FROM C1
)

,C3 AS
(
SELECT Name, TS, GrpNum = FLOOR((ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ts) - 1) / 2 + 1)
FROM C2
WHERE COALESCE(S - (SE - S) - 1, (SE - E) - E) = 0
)

SELECT Name, StartDT = MIN(TS), EndDT = MAX(TS)
FROM C3
GROUP BY Name, GrpNum
ORDER BY Name, GrpNum
;

2

image
image823×347 26.2 KB

This last row is also required since the timestamp is not overlapping - this is correct and we need it

but if we look at the above 2 records/rows we need to get the min start datetime for april 1 as 11am and max end datetime as 330pm since the record/row in the middle for Raj start date time is overlapping with the first record/row that belongs to Raj - I hope i explained it correct

3 
WITH UnWrap
AS
(
	SELECT T.[Name], A.TS
		,SUM(X.StartEnd) AS StartEnd
	FROM #TestTable T
		CROSS JOIN
		(
			VALUES (1), (-1)
		) X (StartEnd)
		CROSS APPLY
		(
			VALUES( IIF(X.StartEnd = 1, T.StartDT, T.EndDT) )
		) A (TS)
	GROUP BY T.[Name], A.TS
)
,StartCounts
AS
(
	SELECT [Name], TS
		,SUM(StartEnd) OVER (PARTITION BY [Name] ORDER BY TS) AS StartCount
	FROM UnWrap
)
,Boundaries
AS
(
	SELECT [Name], TS
		,CASE
			WHEN LAG(StartCount) OVER (PARTITION BY [Name] ORDER BY TS) = 0
			THEN 1
			ELSE 0
		END AS Boundary
	FROM StartCounts
)
,Grps
AS
(
	SELECT [Name], TS
		,SUM(Boundary) OVER (PARTITION BY [Name] ORDER BY TS) AS Grp
	FROM Boundaries
)
SELECT [Name]
	,MIN(TS) AS StartDT
	,MAX(TS) AS EndDT
FROM Grps
GROUP BY [Name], Grp
ORDER BY [Name], StartDT;
4

Hi, Ifor, Thank you so much - I took your complete query and ran it - I only got the record for april 1 with 10am as start date and april 2 10.30 - we need this record because it did not overlap

what are we missing here? - if we look at the rows for Raj
we have 3 records/rows - right? from the 3 records the one with the overalp of

'Raj' ,'04-01-2020 11:00','04-02-2020 02:30' - first record
'Raj' ,'04-01-2020 12:00','04-02-2020 03:30 - second record - the timestamp of start time is 12pm which is overlapping with the first record - since the first record started at 11AM and ended at 230
this 2nd record which started at 12PM (meaning this started after the 1st which one (11am)
so for Raj for April 1 take 11AM as start and 3.30pm as end - this will be one record
and the other record
Raj' ,'04-01-2020 10:00','04-02-2020 10:30' - we will get this and show as is since the start time of this record is not overlapping with the other 2 rows which belong to Raj
really appreciate your help

,('Raj' ,'04-01-2020 11:00','04-02-2020 02:30')
,('Raj' ,'04-01-2020 12:00','04-02-2020 03:30')
,('Raj' ,'04-01-2020 10:00','04-02-2020 10:30')

image

5

From the data provided, all three rows for Raj overlap. ie All three are within 10am on Apr 1 and 10:30 am on Apr 2.

6

Hi Ifor, thank you so much - I would like to explain on what i have been doing
I am trying to calculate the total hrs for the person for that date - for that date there could be many tickets and tickets might be overlapping
what is overlapping? if the start time of the time falls into one of the other ticket for that same day then we take the min of start and max of close so we calculate the total hrs for that person for that day

If the start time is not overlapping for that for that person then just calculate the total hrs using whatever we have in starttime and end time

multiday
some times the ticket is opened on a day and it is closed on the next day or after few days
we will still need to look for the same logic that is see if there are any tickets with start time overlapping
between the start dates - if they are overlapping take the min of start and max of close/end to get the hours - if they are not overlapping calculate as normal

For each person for each day - we do not have to look at the TicketNumber level
I hope this is clear - very complex to understand - below is new script with ticket number in it

drop table #TestTable

SELECT
Name = CONVERT(VARCHAR(10),v.Name)
,TicketNumber = convert(varchar(20),v.TicketNumber)
,StartDT = CONVERT(DATETIME,v.StartDT)
,EndDT = CONVERT(DATETIME,v.EndDT)
INTO #TestTable
FROM (VALUES
--same day start time non overlapping for aman
('Aman','Ticket101','03-01-2024 09:30','03-01-2024 11:30') -- 2 hrs
,('Aman','Ticket201','03-01-2024 12:30','03-01-2024 14:30') -- 2 hrs
,('Aman','Ticket301','03-01-2024 15:00','03-01-2024 15:30') -- 30 mins so the total will be 4 hrs 30 mins
--same day start time overlapping for aman
--so for march 1 for aman total time is 4 hrs 30 mins
,('Aman','Ticket401','03-02-2024 10:00','03-02-2024 14:00') -- started at 10am and closed at 2pm
,('Aman','Ticket401','03-02-2024 12:00','03-02-2024 13:00') -- started at 12pm which is overlapping with the above
,('Aman','Ticket401','03-02-2024 11:00','03-02-2024 15:00') -- started at 11 which is overlapping
--so here we need to see for aman for that date min of start which is 10am and max of end/close is 3pm - 5 hrs
-- so for the March 2 for aman total time is 5 hrs
--more than 1 day
,('Raj' ,'Ticket501','03-03-2024 11:00','03-04-2020 11:00') --started at 11 end at next day 11 - 24 hrs
,('Raj','Ticket601','03-03-2024 12:00','03-04-2024 13:00') -- started at 12pm end at next day 1pm - 25 hrs
--for raj for march 3 11am as min start and max end is 13/1pm --since ticket601 starttime is overlapping
,('Raj','Ticket701','03-03-2024 09:00','03-03-2024 10:00') -- march 3 another ticket which is not overlapping -- start time is not overlapping so 9 to 1 1 hr
)v(Name,TicketNumber,StartDT,EndDT)
;

7

So, for the above data what do you want the results to look like?

8

Hi Ifor , Thanks a for your time. Please use this data in this test table

drop table #TestTable

SELECT
Name = CONVERT(VARCHAR(10),v.Name)
,TicketNumber = convert(varchar(20),v.TicketNumber)
,StartDT = CONVERT(DATETIME,v.StartDT)
,EndDT = CONVERT(DATETIME,v.EndDT)
INTO #TestTable
FROM (VALUES
--same day start time non overlapping for aman
('Aman','Ticket101','03-01-2024 09:30','03-01-2024 11:30') -- 2 hrs
,('Aman','Ticket201','03-01-2024 12:30','03-01-2024 14:30') -- 2 hrs
,('Aman','Ticket301','03-01-2024 15:00','03-01-2024 15:30') -- 30 mins so the total will be 4 hrs 30 mins
,('Raj',' Ticket601','03-01-2024 9:00', '03-01-2024 9:30')
, ('Raj',' Ticket801','03-01-2024 11:00','03-01-2024 13:00')
, ('Raj',' Ticket701','03-01-2024 10:00','03-01-2024 11:40') --this ticket for Raj is overlapping on the same day
,('Raj','Ticket701','03-03-2024 09:00','03-03-2024 10:00')
--same day start time overlapping for aman
,('Aman','Ticket401','03-02-2024 10:00','03-02-2024 14:00') -- started at 10am and closed at 2pm
,('Aman','Ticket401','03-02-2024 12:00','03-02-2024 13:00') -- started at 12pm which is overlapping with the above
,('Aman','Ticket401','03-02-2024 11:00','03-02-2024 15:00') -- started at 11 which is overlapping
--so here we need to see for aman for that date min of start which is 10am and max of end/close is 3pm - 5 hrs
-- so for the March 2 for aman total time is 5 hrs
--more than 1 day
,('Raj' ,'Ticket501','03-03-2024 11:00','03-04-2020 11:00') --started at 11 end at next day 11 - 24 hrs
,('Raj','Ticket601','03-03-2024 12:00','03-04-2024 13:00') -- started at 12pm end at next day 1pm - 25 hrs
--for raj for march 3 11am as min start and max end is 13/1pm --since ticket601 starttime is overlapping

)v(Name,TicketNumber,StartDT,EndDT)

;

This is what i was expecting the output since we do not look at the ticket level calculation - we dont need ticket number in this

Name Year Month Date (from startdate) Hours same day/multiday
Aman 2024 March March 1 2024 4 hours 30 mins same day - Hrs this is the sum for march 1 where the start time is not overlapping for AMan on the same day

Raj 2024 March March 1 2024 30 minutes same day - This is the sum for Raj for March 1 where the start time is not overlapping for Raj on the same day

Raj 2024 March March 1 2024 3 hours same day

  • This 3 hrs is the same day overlapping for Raj (overlapping)

Raj 2024 March March 3 2024 1 hour same day

Note: For Raj we can add for each day

Aman 2024 March March 2 2024 5 hours multiday

  • This is based on the overlapping multiday

Raj 2024 March March 3 2024 2 hours multiday

  • This is based on the overlapping multiday

Note: we have the following scenarios
1).same day - ticket start and end date are on the same day
1.1 within the same day - are there any tickets opverlapping? what is overlappign based on?
for any ticket if the starttime falls into the another ticket's startime then it is overlapping
for ex: ticket101 started at 11am on march 1 2024 then on the same day another ticket
ticket201 started at 12pm
same day flag - will have 2 more flags
same day overlappping
same day non overlapping

2).multiday - ticket started and ended on the next day or any other day after the start day
again we have 2 scenarios to start with
2.1 multiday - multiday overlapping tickets
2.2 multiday - multiday non overlapping tickets - I am not sure this can happen yet, let me find few examples if this can happen or not

9

Name Year Month Date (from start date) Hours same day/multiday
Aman 2024 March March 1 2024 4 hours 30 mins same day
Raj 2024 March March 1 2024 30 minutes same day
Raj 2024 March March 1 2024 3 hours same day
Raj 2024 March March 3 2024 1 hour same day
Aman 2024 March March 2 2024 5 hours multiday
Raj 2024 March March 3 2024 2 hours multiday

10

Sure looked that way to me, as well. I say "looked". They DID overlap.

11

I'm going to suggest that you run Ifor's code on your corrected data as his code is. The previous "mistake" was all your's on the test data.

1 Like
12

I do not understand these results. I think you are going to have to work this out yourself. Good luck.

1 Like
13

yeah, its very difficult to understand _ i see that you could not read the sample - its ok

14 
Hi 

Hope this helps 

DROP TABLE IF EXISTS #TestTable;

SELECT Name = CONVERT(VARCHAR(10), v.Name),
       StartDT = CONVERT(DATETIME, v.StartDT),
       EndDT = CONVERT(DATETIME, v.EndDT)
INTO #TestTable
FROM (VALUES
    ('Aman', '01-02-2020 04:30', '01-02-2020 06:30'),
    ('Aman', '01-02-2020 08:30', '01-02-2020 09:30'),
    ('Aman', '01-02-2020 04:40', '01-02-2020 05:30'),
    ('Aman', '01-02-2020 04:55', '01-02-2020 07:30'),
    ('Aman', '01-02-2020 14:55', '01-02-2020 18:30'),
    ('Aman', '01-02-2020 13:40', '01-02-2020 15:30'),
    ('Ram', '01-02-2020 04:40', '01-02-2020 05:30'),
    ('Ram', '01-02-2020 04:40', '01-02-2020 05:30'),
    ('Raj', '04-01-2020 11:00', '04-02-2020 02:30'),
    ('Raj', '04-01-2020 12:00', '04-02-2020 03:30'),
    ('Raj', '04-01-2020 10:00', '04-02-2020 10:30')
) v(Name, StartDT, EndDT);

WITH Overlaps AS (
    SELECT 
        Name,
        MIN(StartDT) AS MinStartDT,
        MAX(EndDT) AS MaxEndDT
    FROM #TestTable t1
    WHERE EXISTS (
        SELECT 1 
        FROM #TestTable t2 
        WHERE t1.Name = t2.Name 
        AND t1.StartDT < t2.EndDT 
        AND t1.EndDT > t2.StartDT 
        AND CAST(t1.StartDT AS DATE) <> CAST(t2.StartDT AS DATE)
    )
    GROUP BY Name
)
SELECT * FROM Overlaps;