Finding Min and max date of overlapping date ranges in SQL Server

SQL Server Administration
,
1

Kindly help me writing a query for below problem.

I WANT Min start date time and max end date time of overlapping date ranges and same start & end date time for not overlapped date ranges in SQL Server

Example:- My_table

NAME Start Date Time End DAte time
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

Expected result will be only overlapped date and time:

(minimum and maximum time between all overlapped)

NAME Start Date Time End Date time
Aman 01-02-2020 04:30 01-02-2020 07:30
Aman 01-02-2020 08:30 01-02-2020 09:30
Aman 01-02-2020 13:40 01-02-2020 18:30
Ram 01-02-2020 04:40 01-02-2020 05:30

First row of Result Values has overlapped date ranges so we right min and max of that all overlapped date ranges of that Name.
Second row has no any overlap so we write it as it is ..
Third row is overlapping so we write min and max of overlapped dates of Aman.
Fourth row is also overlapping so we right it once with min and max date range.

Kindly help me in this asap, I already wasted 5 days in searching solution of this ...

2

Hi and welcome aboard.

Just a tip for future posts... you did a really nice job in your post but if you post what is known as "Readily Consumable Data" where the people trying to help you only need to run a snippet of code and they have a test table with your data in it, the people trying to help you will very much appreciate it and you're likely to get help more quickly.

Here's just one many ways you can do such a thing... you could also use a separate CREATE TABLE statement and a SELECT FROM VALUES to populate the table.

 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')
        )v(Name,StartDT,EndDT)
;

Here's pretty fast method to solve your problem...

   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
;

To read more about how that code works, please see the article written by the original author. The code I used is a modification for his Solution 2.

http://blogs.solidq.com/en/sqlserver/packing-intervals/

3

For those that are now disappointed by the link that now produces a "404" (I have no idea what happened to "SolidQ" as a company), here's a link to a very similar article by the same author. I've also followed that with an updated method, also by the same author.

Solutions to Packing Date and Time Intervals Puzzle | ITPro Today: IT News, How-Tos, Trends, Case Studies, Career Tips, More.

https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem

I also hate sites that won't allow the author of a post make such updates to their original posts after a certain period of time has passed...

YOU HEAR THAT SQLTEAM MONITORS!!!???