CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
GraemeMiller
GraemeMiller

Reputation: 12253

How can I prevent date overlaps in SQL?

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.

Upvotes: 5

Views: 4250

Answers (3)

GraemeMiller
GraemeMiller

Reputation: 12253

I am now using domain driven design techniques. This keeps me from worrying about database triggers etc. and keeps all the logic within the .Net code. Answer is here so that people can see an alternative.

I treat the parent of the collection of periods as an aggregate root and the root has a timestamp. Aggregate root is a consistency boundaries for transactions, distributions and concurrency (see Evans - what I've learned since the blue book). This is used to check the last time any change was confirmed to the database.

I then have methods to add the hire periods to the parent. I test on overlap when adding the movement to the aggregate root. e.g. AddHire(start,end) - will validate that this creates no overlap on the in memory domain object.

AS there is no overlap I save the changes (via my repository), and check the database timestamp is still the same as at the start of the process. Assuming timestamp is the same as it was when I retrieved the entity the changes are persisted and the database updates the timestamp.

If someone else tries to save changes when the aggregate root is being worked on then either I will commit first or they will. If I commit first the timestamps will not match and the overlap check will re-run to make sure that they haven't created an overlap in the intervening time.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

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;

Upvotes: 3

vulkanino
vulkanino

Reputation: 9134

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

Upvotes: 3

Related Questions