CODEWITHSUNDEEP
sqlsql-serverdateoverlap
BohdanZPM
BohdanZPM

Reputation: 755

SQL Server - check if sets of time ranges overlap each other

I have two tables with time ranges, Weeks and Periods: Weeks:

WeekID | StartDate  | EndDate    |
1      | 2017-01-01 | 2017-01-07 |
2      | 2017-01-08 | 2017-01-14 |
3      | 2017-01-15 | 2017-01-21 |

and Periods:

PeriodID | StartDate  | EndDate    |
1        | 2016-12-01 | 2017-01-03 |
2        | 2017-01-20 | 2017-02-28 |
3        | 2017-03-15 | 2017-03-16 |

I need to have only those records in table Weeks, that have some common dates with date ranges from Periods table. In other words, I need to delete from Weeks where time between StartDate and EndDate does not have any date in common with any of the time periods from periods table. Any thoughts how to do that?

The result Weeks table should be:

WeekID | StartDate  | EndDate    |
1      | 2017-01-01 | 2017-01-07 |
3      | 2017-01-15 | 2017-01-21 |

Any thoughts on this will help.

Upvotes: 1

Views: 72

Answers (1)

SqlZim
SqlZim

Reputation: 38073

This will return Weeks that do not overlap a Period:

select *
from Weeks w
where not exists (
    select 1
    from Weeks l
      inner join Periods r
        on l.EndDate >= r.StartDate
       and r.EndDate >= l.StartDate
    where w.WeekId = l.WeekId 
  )

rextester demo: http://rextester.com/VYTEC19325

returns: 

+--------+------------+------------+
| WeekId | StartDate  |  EndDate   |
+--------+------------+------------+
|      2 | 2017-01-08 | 2017-01-14 |
+--------+------------+------------+

To delete those Weeks, change select * to delete.

To just select Weeks that overlap Periods change not exists() to exists().

Upvotes: 1

Related Questions