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