I have been working on a solution for detecting overlapping timespans in SQL Server. This is to prevent the overlap of events, or in my case, work shifts.
While there has been a lot of discussion on SO for finding overlapping date ranges, there is less on overlapping timespans, and even less when the timespans are allowed to cross midnight. Through lots of research and testing, I've come up with the following solution.
CREATE TABLE Shift
(
ShiftID INT IDENTITY(1,1),
StartTime TIME(0) NOT NULL,
EndTime TIME(0) NOT NULL
);
CREATE PROCEDURE [dbo].[spInsertShift]
@StartTime TIME(0),
@EndTime TIME(0)
AS
BEGIN
DECLARE @ThrowMessage NVARCHAR(4000)
-- Check whether the new shift would overlap with any existing shifts.
IF EXISTS
(
SELECT 0
FROM Shift
WHERE
(
-- Case #1: Neither shift crosses midnight.
(@StartTime < @EndTime AND StartTime < EndTime)
-- New shift would overlap with an existing shift.
AND @StartTime < EndTime
AND StartTime < @EndTime
)
OR
(
-- Case #2: Both shifts cross midnight.
(@EndTime < @StartTime AND EndTime < StartTime)
-- New shift would overlap with an existing shift.
AND CAST(@StartTime AS DATETIME) < DATEADD(DAY, 1, CAST(EndTime AS DATETIME))
AND CAST(StartTime AS DATETIME) < DATEADD(DAY, 1, CAST(@EndTime AS DATETIME))
)
OR
(
-- Case #3: New shift crosses midnight, but the existing shift does not.
(@EndTime < @StartTime AND StartTime < EndTime)
AND
(
-- New shift would overlap with an existing shift.
@StartTime > StartTime AND @StartTime < EndTime
OR @EndTime > StartTime AND @EndTime < EndTime
OR
(
-- Existing shift would be inside new shift.
CAST(StartTime AS DATETIME) BETWEEN CAST(@StartTime AS DATETIME) AND DATEADD(DAY, 1, CAST(@EndTime AS DATETIME))
AND CAST(EndTime AS DATETIME) BETWEEN CAST(@StartTime AS DATETIME) AND DATEADD(DAY, 1, CAST(@EndTime AS DATETIME))
)
)
)
OR
(
-- Case #4: New shift does not cross midnight, but the existing shift does.
(@StartTime < @EndTime AND EndTime < StartTime)
AND
(
-- Existing shift would overlap with new shift.
StartTime > @StartTime AND StartTime < @EndTime
OR EndTime > @StartTime AND EndTime < @EndTime
OR
(
-- New shift would be inside an existing shift.
CAST(@StartTime AS DATETIME) BETWEEN CAST(StartTime AS DATETIME) AND DATEADD(DAY, 1, CAST(EndTime AS DATETIME))
AND CAST(@EndTime AS DATETIME) BETWEEN CAST(StartTime AS DATETIME) AND DATEADD(DAY, 1, CAST(EndTime AS DATETIME))
)
)
)
)
BEGIN
SET @ThrowMessage = 'A Shift already exists in this timespan.'
THROW 50140, @ThrowMessage, 1
END
INSERT INTO Shift
(
StartTime,
EndTime
)
VALUES
(
@StartTime,
@EndTime
)
END
Since my timespans are allowed to cross midnight, I had to use some mechanism to denote that the EndTime is greater than the StartTime, despite the fact that StartTime='22:00' > EndTime='06:00' for example. I chose to CAST the EndTime to a DATETIME and add a day, in these cases.
My question is: what is the best way to detect overlapping timespans that are allowed to cross midnight? My current solution feels overly verbose and complex. When testing, please keep all timespan test cases in mind. Thanks!
