create table #Overlaps ( >>PK int identity >>,employeePK int >>,StartDate Date >>,Enddate Date) >> >>insert into #Overlaps >>(employeePK, StartDate, EndDate) >>VALUES (1, '2013-01-01', '2016-08-31') -- this overlaps with the next row >>,(1, '2014-08-01', '2014-08-31') -- this overlaps with previous as both start date '2014-08-01' is greater than '2013-01-01' and less than '2016-08-31' and end date '2014-08-31' is greater than '2013-01-01' and less than '2016-08-31' >>,(1, '2016-09-01', '2017-08-31') -- no overlap here >>,(2, '2014-01-01', '2014-05-31') -- this overlaps with the next row >>,(2, '2014-03-01', '2014-12-31') -- this overlaps with the previous row >> >>select * from #Overlaps >> >>drop table #Overlaps>>
select o1pk = o1.pk >> ,o1Emp = o1.employeepk >> ,o1Start = o1.startdate >> ,o1End = o1.Enddate >> ,o2pk = o2.pk >> ,o2emp = o2.employeepk >> ,o2Start = o2.Startdate >> ,o2End = o2.Enddate >>from #Overlaps o1 >> inner join #Overlaps o2 on o1.employeepk = o2.employeepk >> and (o1.startdate between o2.startdate and o2.enddate or o1.enddate between o2.startdate and o2.Enddate) >> and o1.pk <> o2.pk