0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
salvationishere
  • 3,461
  • 29
  • 104
  • 143

2 Answers2

3

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
JBrooks
  • 9,901
  • 2
  • 28
  • 32
1

Is below working for your scenario? 

declare @staff_records table
(
tmpKey int identity(1,1),
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

select * 
from @staff_records t1
where exists
(
select 1 from @staff_records t2 
where t2.staff_id = t1.staff_id and t2.tmpKey <> t1.tmpKey
    and (t2.start_date between t1.start_date and t1.end_date OR t2.end_date between t1.start_date and t1.end_date)
)
cqi
  • 539
  • 3
  • 13