How can I prevent date overlaps in SQL?

Question

I have the following table structure for a hire table:

hireId int primary key
carId int not null foreign key
onHireDate datetime not null
offHireDate datetime not null

I am attempting to program a multi-user system that does not allow onhire and offhire period for cars to overlap. I need to be able to add hires in a non sequential order. Also need to allow editing of hires.

Any way to constrain the tables or use triggers etc to prevent overlaps? I am using entity framework so I would want to insert to the table as normal and then if it fails throw some catchable exception etc.

Answer 1

Consider this query:

SELECT *
FROM Hire AS H1, Hire AS H2
WHERE H1.carId = H2.carId
AND H1.hireId < H2.hireId 
AND 
   CASE 
   WHEN H1.onHireDate > H2.onHireDate THEN H1.onHireDate 
   ELSE H2.onHireDate END
   <
   CASE 
   WHEN H1.offHireDate > H2.offHireDate THEN H2.offHireDate 
   ELSE H1.offHireDate END

If all rows meet you business rule then this query will be the empty set (assuming closed-open representation of periods i.e. where the end date is the earliest time granule that is not considered within the period).

Because SQL Server does not support subqueries within CHECK constraints, put the same logic in a trigger (but not an INSTEAD OF trigger, unless you can provide logic to resolve overlaps).

---

Alternative query using Fowler:

SELECT *
  FROM Hire AS H1, Hire AS H2
 WHERE H1.carId = H2.carId
       AND H1.hireId < H2.hireId 
       AND H1.onHireDate < H2.offHireDate 
       AND H2.onHireDate < H1.offHireDate;

Answer 2

CREATE TRIGGER tri_check_date_overlap ON your_table
INSTEAD OF INSERT 
AS
BEGIN
    IF @@ROWCOUNT = 0
       RETURN

    -- check for overlaps in table 'INSERTED'
    IF EXISTS(
        SELECT hireId FROM your_table WHERE 
             (INSERTED.onHireDate BETWEEN onHireDate AND offHireDate) OR
             (INSERTED.offHireDate BETWEEN onHireDate AND offHireDate) 
    )
        BEGIN   
           -- exception? or do nothing?
        END
    ELSE
        BEGIN
        END
END
GO