CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Confounder
Confounder

Reputation: 555

SQL Server Extract overlapping date ranges (return dates that cross other dates)

How would I go about extracting the overlapping dates from the following table?

ID  Name        StartDate   EndDate     Type
==============================================================
1   John Smith  01/01/2014  31/01/2014  A
2   John Smith  20/01/2014  20/02/2014  B
3   John Smith  01/03/2014  28/03/2014  A
4   John Smith  18/03/2014  24/03/2014  B
5   John Smith  01/07/2014  31/07/2014  A
6   John Smith  15/07/2014  31/07/2014  B
7   John Smith  25/07/2014  25/08/2014  C

Based on the first example for John Smith, the dates 01/01/2014 to 31/01/2014 overlap with 20/01/2014 to 20/02/2014, so I am expecting just overlapping period back which is 20/01/2014 to 31/01/2014.

The final result would be:

ID  Name        StartDate   EndDate
==================================================
8   John Smith  20/01/2014  31/01/2014
9   John Smith  18/03/2014  24/03/2014
10  John Smith  15/07/2014  31/07/2014
11  John Smith  25/07/2014  31/07/2014

HELP REQUIRED 10 August 2014

In addition to the above request, I am looking for help or guidance on how to get the following results which should include the dates that overlap and the dates that don't. The ID column is irrelevant.

ID  Name        StartDate   EndDate     Type
==================================================
1   John Smith  01/01/2014  19/01/2014  A
8   John Smith  20/01/2014  31/01/2014  AB
2   John Smith  01/02/2014  20/02/2014  B
3   John Smith  01/03/2014  17/03/2014  A
9   John Smith  18/03/2014  24/03/2014  AB
3   John Smith  25/03/2014  28/03/2014  A
5   John Smith  01/07/2014  14/07/2014  A
10  John Smith  15/07/2014  31/07/2014  AB
11  John Smith  25/07/2014  31/07/2014  ABC
7   John Smith  01/08/2014  25/08/2014  C

Although the following image is not an exact reflection of the above, for illustration purposes, I am interested in seeing the dates that overlap (red) and the dates that don't (sky blue) in the same result set.

https://i.sstatic.net/11lnO.jpg

Upvotes: 0

Views: 342

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you want just overlapping periods, you can get this with a self join. Do note that the results might be redundant if more than two periods overlap on certain dates.

select ft.name,
       (case when max(ft.startdate) > max(ft2.startdate) then max(ft.startdate)
             else max(ft2.startdate)
        end) as startdate,
       (case when min(ft.enddate) > min(ft2.enddate) then min(ft.enddate)
             else min(ft2.enddate)
        end) as enddate
from followingtable ft join
     followingtable ft2
     on ft.name = ft2.name and
        ft.id < ft2.id and
        ft.startdate <= ft2.enddate and
        ft.enddate > ft2.startdate
group by ft.name, ft.id, ft2.id;

This doesn't assign the ids. You can do that with row_number() and an offset.

Upvotes: 1

Related Questions