CODEWITHSUNDEEP
sql-serverdaterangeconstraints
Tom K
Tom K

Reputation: 41

SQL Server contraints for date ranges

I am trying to constrain a SQL Server Database by a Start Date and End Date such that I can never double book a resource (i.e. no overlapping or duplicate reservations).

Assume my resources are numbered such that the table looks like ResourceId, StartDate, EndDate, Status

So lets say I have resource #1. I want to make sure that I cannot have have the a reservation for 1/8/2017 thru 1/16/2017 and a separate reservation for 1/10/2017 - 1/18/2017 for the same resource.

A couple of more complications, a StartDate for a resource can be the same as the EndDate for the resource. So 1/8/1027 thru 1/16/2017 and 1/16/2017 thru 1/20/2017 is ok (i.e., one person can check in on the same day another person checkouts).

Furthermore, the Status field indicates whether the booking of the resource is Active or Cancelled. So we can ignore all cancelled reservations.

We have protected against these overlapping or double booking reservations in Code (Stored Procs and C#) when saving but we are hoping to add an extra layer of protection by adding a DB Contraint.

Is this possible in SQL Server ?

Thanks in Advance

Upvotes: 3

Views: 2006

Answers (2)

SqlZim
SqlZim

Reputation: 38033

Drawing from this answer here: Date range overlapping check constraint.

First, check to make sure there are not existing overlaps:

select *
from dbo.Reservation as r
where exists (
  select 1
  from dbo.Reservation i
  where i.PersonId = r.PersonId
    and i.ReservationId != r.ReservationId
    and isnull(i.EndDate,'20990101') > r.StartDate
    and isnull(r.EndDate,'20990101') > i.StartDate
  );
go

If it is all clear, then create your function.

There are a couple of different ways to write the function, e.g. we could skip the StartDate and EndDate and use something based only on ReservationId like the query above, but I will use this as the example:

create function dbo.udf_chk_Overlapping_StartDate_EndDate (
    @ResourceId int
  , @StartDate date
  , @EndDate date
) returns bit as
begin;
  declare @r bit = 1;
  if not exists (
    select 1
    from dbo.Reservation as r
    where r.ResourceId = @ResourceId
      and isnull(@EndDate ,'20991231') > r.StartDate
      and isnull(r.EndDate,'20991231') >  @StartDate
      and r.[Status] = 'Active'
    group by r.ResourceId
    having count(*)>1
  )
  set @r = 0;
  return @r;
end;
go

Then add your constraint:

alter table dbo.Reservation 
  add constraint chk_Overlapping_StartDate_EndDate 
    check (dbo.udf_chk_Overlapping_StartDate_EndDate(ResourceId,StartDate,EndDate)=0);
go

Last: Test it.

Upvotes: 2

Tim
Tim

Reputation: 6060

You can use a CHECK constraint to make sure startdate is on or before EndDate easily enough: 

CONSTRAINT [CK_Tablename_ValidDates] CHECK ([EndDate] >= [StartDate])

A constraint won't help with preventing an overlapping date range. You can instead use a TRIGGER to enforce this by creating a FOR INSERT, UPDATE trigger that rolls back the transaction if it detects a duplicate:

CREATE TRIGGER [TR_Tablename_NoOverlappingDates] FOR INSERT, UPDATE AS
IF EXISTS(SELECT * from inserted INNER JOIN [MyTable] ON blah blah blah ...) BEGIN 
    ROLLBACK TRANSACTION;
    RAISERROR('hey, no overlapping date ranges here, buddy', 16, 1);
    RETURN;
END

Another option is to create a indexed view that finds duplicates and put a unique constraint on that view that will be violated if more than 1 record exists. This is usually accomplished with a dummy table that has 2 rows cartesian joined to an aggregate view that selects the duplicate id-- thus one record with a duplicate would return two rows in the view with the same fake id value that has a unique index.

I've done both, I like the trigger approach better.

Upvotes: 1

Related Questions