SQL Server: Finding the date range

Question

I have two tables D1 and D2

DI has:

ID   num   startdate    enddate     Status
P10  B123  2002-01-01   2009-04-06  NULL
P11  B124  2006-05-06   2008-07-01  NULL
P12  B125  2010-05-05   2015-04-04  NULL

D2 has: (reference table)

ID   num   startdate    enddate     
P10  B123  2010-01-01   2012-04-06  
P11  B124  2007-05-06   2010-07-01  
P12  B125  2007-05-05   2008-04-04 

Now, update status in D1 as 'fail' if for same [num] in D2, it has overlapping date range. Date range is enddate - startdate.

So any date range in D1 with same [num] shouldn't touch or exist in date ranges of table D2.

Expected result:

ID   num   startdate    enddate     Status
P10  B123  2002-01-01   2009-04-06  NULL
P11  B124  2006-05-06   2008-07-01  FAIL
P12  B125  2010-05-05   2015-04-04  NULL

Thanks

Answer 1

Try This:

  Update D1 Set Status = 'Fail'
  From D1 a
  Where exists 
     (Select * From D2 b
      Where b.num = a.num
         And b.enddate >= a.startDate
            And b.startDate <= a.enddate)