0

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.

p2k
  • 2,126
  • 4
  • 23
  • 39
user1480192
  • 665
  • 8
  • 23

1 Answers1

1

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

select a.*
from table_a a
where not exists (select 1
                  from table_b b
                  where a.from_date < b.to_date and a.to_date > b.from_date
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786