I have a table with multiple rows per staff person. Each of these rows has staff_id, start_date, and end_date. Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical. How can I do this? I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records')
) DROP TABLE #staff_records;
create table #staff_records
(
staff_id varchar(max),
start_date datetime,
end_date datetime
)
insert #staff_records values('AA-22','2/1/15','2/4/15')
insert #staff_records values('AA-22','2/5/15','2/6/15')
insert #staff_records values('AA-22','2/9/15','2/13/15')
insert #staff_records values('AA-22','2/4/15','2/16/15')
insert #staff_records values('AA-22','1/25/15','2/2/15')
insert #staff_records values('BB-22','2/1/15','3/1/15')
insert #staff_records values('BB-22','3/1/15','4/1/15')
select * from #staff_records order by staff_id, start_date desc
If you don't want to flag records where the start_date is the same as the previous end_date, it would be:
SELECT a.*
FROM staff_records AS a
JOIN staff_records AS b
ON a.staff_id = b.staff_id
AND a.start_date < b.end_date
AND b.start_date < a.end_date
Picture a timeline:
a s----------e
b s----------------e
The WHERE checks if they overlap, but it doesn't flag if the start_date and end_date are equal. If you do want to flag rows where start_date and end_date are equal (and you have an ID column for your rows), the last 2 lines would change to:
AND a.ID > b.ID
AND a.start_date <= b.end_date
AND b.start_date <= a.end_date
a s----------e
b s----------------e