Shawn's Ramblings

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');

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');

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