sql server - Check for Overlapping date on Insert/Update -

i have table holds list of dates , more data person. table should never have undeleted overlapping rows (dates overlapping).

is there way can put check constraint on table, ensure when update or insert row, there's no overlapping details?

below cut down version of table. has deleted flag, , start/end dates. 'null' end date means it's ongoing.

i provide legal, , not-so-legal inserts (and why they're legal , illegal).

declare @test table (     id int not null identity(1,1),     personid int not null,     startdate date not null,     enddate date null,     deleted bit not null )   insert @test (personid, startdate, enddate, deleted) select 1, '01-jan-2015', '15-jan-2015', 0 union -- valid select 1, '16-jan-2015', '20-jan-2015', 1 union -- valid , deleted select 1, '18-jan-2015', null, 0 union -- valid select 2, '01-jan-2015', null, 0 union -- valid.. never ending row. select 2, '18-jan-2015', '30-jan-2015', 0 union -- invalid! overlaps above record. select 2, '20-jan-2015', '30-jan-2015', 1 union -- valid, it's deleted (still overlaps, though) select 3, '01-jan-2015', '10-jan-2015', 0 union -- valid select 3, '10-jan-2015', null, 0 -- invalid, overlaps last , first days  select * @test 

i need make sure table doesn't allow overlapping dates same person, undeleted rows.

for date range check, use "(starta <= endb) , (enda >= startb)" formula, unsure how check constraint, , across multiple rows.

i may need trigger, checking inserted.values exiting, , somehow, cancel if find matches?

you cannot use check constraint without adding additional columns. have create trigger check if inserted date ranges non overlapping. this..

create trigger [dbo].[daterangetrigger]    on  [dbo].test after insert, update  begin declare @maxdate date = '2999/12/31'     if exists (select t.startdate, t.enddate test t             join inserted             on i.personid = t.personid             , i.id <> t.id             and(                  (i.startdate > t.startdate , i.startdate < isnull(t.enddate,@maxdate))                 or (isnull(i.enddate,@maxdate) < isnull(t.enddate,@maxdate) , isnull(i.enddate,@maxdate) > t.startdate)                 or (i.startdate < t.startdate , isnull(i.enddate,@maxdate) > isnull(t.enddate,@maxdate))                )             t.deleted = 0 , i.deleted = 0             )     begin         raiserror ('inserted date within invalid range', 16, 1)         if (@@trancount>0)             rollback     end  end 

you can refer 1 of these threads more information

enforcing unique date range fields in sql server 2008

unique date range fields in sql server 2008