Recursive CTEs: Multi-day schedule entries & conflict detection
Let's say you have a table of schedule entries for individuals. A given entry would of course have a start date and time, and an end date and time. Some entries span multiple days.
CREATE TABLE dbo.Person (
personId INT PRIMARY KEY,
firstName NVARCHAR(50) NOT NULL,
lastName NVARCHAR(100) NOT NULL
);
INSERT dbo.Person
VALUES
(1, N'Bruce', N'Bardly'),
(2, N'Jess', N'Abracadabra'),
(3, N'Tom', N'Calzone'),
(4, N'Rebecca', N'Malarky');
CREATE TABLE dbo.Schedule (
scheduleId INT PRIMARY KEY,
personId INT NOT NULL REFERENCES Person(personId),
scheduleDescription NVARCHAR(100) NOT NULL,
datetimeStart DATETIME NOT NULL,
datetimeEnd DATETIME NOT NULL
);
INSERT dbo.Schedule
VALUES
(1, 1, N'Roll some dice', '2018-09-01 09:00', '2018-09-01 10:00'),
(2, 1, N'Slay an orc', '2018-09-02 09:00', '2018-09-02 12:00'),
(3, 1, N'Insult a wizard', '2018-09-03 12:00', '2018-09-03 12:15'),
(4, 1, N'Run from a dragon', '2018-09-05 18:00', '2018-09-05 18:30'),
(5, 1, N'Play lute', '2018-09-01 09:00', '2018-09-07 17:00'),
(6, 2, N'Brew a potion', '2018-09-01 09:00', '2018-09-02 17:00'),
(7, 2, N'Be insulted by Bruce', '2018-09-03 12:00', '2018-09-03 12:15'),
(8, 2, N'Practice lightning', '2018-09-04 09:00', '2018-09-04 12:00'),
(9, 2, N'Meditate', '2018-09-06 22:00', '2018-09-07 06:00'),
(10, 2, N'Set some stuff on fire idk', '2018-09-04 11:00', '2018-09-04 13:00'),
(11, 3, N'Make some pasta', '2018-09-01 09:00', '2018-09-01 17:00'),
(12, 3, N'Make a pizza', '2018-09-03 13:00', '2018-09-03 16:00'),
(13, 3, N'Make minestrone', '2018-09-01 06:00', '2018-09-01 12:00'),
(14, 3, N'Make bruschetta', '2018-09-01 09:00', '2018-09-01 17:00'),
(15, 3, N'Roast garlic', '2018-09-07 09:00', '2018-09-07 10:00'),
(16, 4, N'Do nothing', '2018-09-04 09:00', '2018-09-07 17:00');
For the sake of a complete example, let's also say that for certain people, you have default start and end times (such as in a 9-5 office environment).
CREATE TABLE dbo.DefaultHours (
personId INT NOT NULL REFERENCES Person (personId),
defaultStart TIME NOT NULL,
defaultEnd TIME NOT NULL
);
INSERT dbo.DefaultHours
VALUES (4, '09:00', '17:00');
You want to be able to easily display these on a day-by-day basis (like on a calendar), and you want to able to detect conflicting schedule entries. The best way I've found to do this it to use a recursive CTE to artificially generate rows for 'in-between' days.
WITH schedCte AS (
SELECT
scheduleId,
Schedule.personId,
scheduleDescription,
CAST(datetimeStart AS DATE) AS schedDate,
CAST(datetimeStart AS TIME) AS timeStart,
CAST(dateTimeEnd AS DATE) AS dateEnd,
-- If the entry ends on the same day it starts,
-- this is just a regular entry
-- Else, use the default end time for the user / NULL
CASE
WHEN CAST(datetimeStart AS DATE) <> CAST(dateTimeEnd AS DATE)
THEN defaultEnd
ELSE CAST(dateTimeEnd AS TIME)
END AS timeEnd,
CAST(dateTimeEnd AS TIME) AS timeEndOrig,
defaultStart,
defaultEnd
FROM Schedule
LEFT JOIN DefaultHours
ON Schedule.personId = DefaultHours.personId
UNION ALL
SELECT
scheduleId,
personId,
scheduleDescription,
DATEADD(DAY, 1, schedDate) AS dateStart,
-- 'In-between' / final date record, use default start time
defaultStart AS timeStart,
dateEnd,
CASE
WHEN DATEADD(DAY, 1, schedDate) <> dateEnd
THEN defaultEnd
ELSE timeEndOrig
END AS timeEnd,
timeEndOrig,
defaultStart,
defaultEnd
FROM schedCte
-- We're interating thru until all multi-day entries have have their
-- 'in-between' days generated, and we've reached their dateEnd
WHERE schedDate < dateEnd
)
SELECT
CONCAT(firstName, ' ', SUBSTRING(lastName, 1, 1), '.') AS personName,
schedDate,
timeStart,
timeEnd,
scheduleDescription,
-- Fancy: comma-separated descriptions of conflicting entries
SUBSTRING((
SELECT '= ' + s2.scheduleDescription
FROM schedCte s2
WHERE s1.personId = s2.personId
AND s1.schedDate = s2.schedDate
-- don't cause self-conflict
AND s1.scheduleId <> s2.scheduleId
-- bug: records starting at 00:00 / ending at 23:59 cause issues
AND ISNULL(s1.timeStart, '00:00') < ISNULL(s2.timeEnd, '23:59')
AND ISNULL(s1.timeEnd, '23:59') > ISNULL(s2.timeStart, '00:00')
ORDER BY s2.timeStart, s2.scheduleDescription
FOR XML PATH ('')
), 3, 5000) AS scheduleConflicts
FROM schedCte s1
JOIN Person
ON s1.personId = Person.personId
ORDER BY personName, schedDate, timeStart
personName | schedDate | timeStart | timeEnd | scheduleDescription | scheduleConflicts |
---|---|---|---|---|---|
Bruce B. | 2018-09-01 | 09:00:00 | 10:00:00 | Roll some dice | Play lute |
Bruce B. | 2018-09-01 | 09:00:00 | NULL | Play lute | Roll some dice |
Bruce B. | 2018-09-02 | NULL | NULL | Play lute | Slay an orc |
Bruce B. | 2018-09-02 | 09:00:00 | 12:00:00 | Slay an orc | Play lute |
Bruce B. | 2018-09-03 | NULL | NULL | Play lute | Insult a wizard |
Bruce B. | 2018-09-03 | 12:00:00 | 12:15:00 | Insult a wizard | Play lute |
Bruce B. | 2018-09-04 | NULL | NULL | Play lute | NULL |
Bruce B. | 2018-09-05 | NULL | NULL | Play lute | Run from a dragon |
Bruce B. | 2018-09-05 | 18:00:00 | 18:30:00 | Run from a dragon | Play lute |
Bruce B. | 2018-09-06 | NULL | NULL | Play lute | NULL |
Bruce B. | 2018-09-07 | NULL | 17:00:00 | Play lute | NULL |
Jess A. | 2018-09-01 | 09:00:00 | NULL | Brew a potion | NULL |
Jess A. | 2018-09-02 | NULL | 17:00:00 | Brew a potion | NULL |
Jess A. | 2018-09-03 | 12:00:00 | 12:15:00 | Be insulted by Bruce | NULL |
Jess A. | 2018-09-04 | 09:00:00 | 12:00:00 | Practice lightning | Set some stuff on fire idk |
Jess A. | 2018-09-04 | 11:00:00 | 13:00:00 | Set some stuff on fire idk | Practice lightning |
Jess A. | 2018-09-06 | 22:00:00 | NULL | Meditate | NULL |
Jess A. | 2018-09-07 | NULL | 06:00:00 | Meditate | NULL |
Rebecca M. | 2018-09-04 | 09:00:00 | 17:00:00 | Do nothing | NULL |
Rebecca M. | 2018-09-05 | 09:00:00 | 17:00:00 | Do nothing | NULL |
Rebecca M. | 2018-09-06 | 09:00:00 | 17:00:00 | Do nothing | NULL |
Rebecca M. | 2018-09-07 | 09:00:00 | 17:00:00 | Do nothing | NULL |
Tom C. | 2018-09-01 | 06:00:00 | 12:00:00 | Make minestrone | Make bruschetta, Make some pasta |
Tom C. | 2018-09-01 | 09:00:00 | 17:00:00 | Make bruschetta | Make minestrone, Make some pasta |
Tom C. | 2018-09-01 | 09:00:00 | 17:00:00 | Make some pasta | Make minestrone, Make bruschetta |
Tom C. | 2018-09-03 | 13:00:00 | 16:00:00 | Make a pizza | NULL |
Tom C. | 2018-09-07 | 09:00:00 | 10:00:00 | Roast garlic | NULL |