user1480192 user1480192 - 2 months ago 11
SQL Question

SQL to remove time overlaps between two tables

I have two tables. Each table has two fields: from_date and to_date. I need to find all the records in table A that do not overlap records in table B.

I am using MSSQL 2008.

CREATE TABLE Table_A(from_date datetime , to_date datetime )

CREATE TABLE Table_B(from_date datetime , to_date datetime )

Insert into Table_A (from_date, to_date) values ('2016-09-01 10:00:00','2016-09-01 11:00:00')

Insert into Table_A (from_date, to_date) values ('2016-09-01 11:00:00','2016-09-01 12:00:00')

Insert into Table_A (from_date, to_date) values ('2016-09-01 12:00:00','2016-09-01 13:00:00')

Insert into Table_B (from_date, to_date) values ('2016-09-01 10:00:00','2016-09-01 12:00:00')

Insert into Table_B (from_date, to_date) values ('2016-09-01 13:00:00','2016-09-01 14:00:00')

Insert into Table_B (from_date, to_date) values ('2016-09-01 14:00:00','2016-09-01 15:00:00')


The result should be only the third record in Table A (1200-1300) because it does not overlap any record in Table B.

Answer

If you want no overlaps, then here is one way:

select a.*
from table_a a
where not exists (select 1
                  from table_b
                  where a.from_date < b.to_date and a.to_date > b.from_date
                 );