Thanks, but my primary keys are actually unique identifiers​.
>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')
>>,(1, '2014-08-01', '2014-08-31')
>>,(1, '2016-09-01', '2017-08-31')
>>,(2, '2014-01-01', '2014-05-31')
>>,(2, '2014-03-01', '2014-12-31')
>>
>>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