Shawn's Ramblings

Sep 02, 2018

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