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