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