You might want to replace

o1.pk <> o2.pk

by

o1.pk<o2.pk

, if you are returning the same overlap twice. And if you do you also need to check if a "lap" falls completely within the other "lap".

>Hi,
>
>I have been tasked with finding out invalid data in a database. This is a simplified scenario:
>
>

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

>
>I need a query that will pull out employee 1 having overlaps of dates in the first 2 rows and employee 2's both records overlapping. Note Employee 1's 3rd record is fine.
>
>Basically these are contracts and the start and end date cannot overlap with another contract for the same employee and I need to pull out all the records that do overlap for each employee so they can manually fix them.
>
>This might do it, can anyone see any problems here?
>
>

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