Subtracting datetime ranges from other datetime ranges (T-SQL)

Question

I need some help to create a SQL query. At least a hint on how to do this. I have a table Schedule with datetime ranges and another table UnavailableTimes also with datetime ranges. From this I want the available times, by subtracting the unavailable times from the schedule.

T-SQL (test data)

CREATE TABLE Schedule (
    ID INT,
    StartTime DATETIME,    
    EndTime DATETIME
)

CREATE TABLE UnavailableTimes (
    ID INT,
    StartTime DATETIME,    
    EndTime DATETIME
)

CREATE TABLE AvailableTimes (
    ID INT,
    StartTime DATETIME,    
    EndTime DATETIME
)

INSERT INTO Schedule (ID, StartTime, EndTime)
VALUES 
    (2, '2018-02-27 08:00', '2018-02-27 12:00'),
    (2, '2018-02-27 13:00', '2018-02-27 17:00')

INSERT INTO UnavailableTimes (ID, StartTime, EndTime)
VALUES 
    (2, '2018-02-27 07:30', '2018-02-27 08:30'),
    (2, '2018-02-27 08:00', '2018-02-27 09:30'),
    (2, '2018-02-27 13:30', '2018-02-27 14:00'),
    (2, '2018-02-27 16:00', '2018-02-27 18:00')

Schedule

ID  StartTime           EndTime  
2   2018-02-27 08:00    2018-02-27 12:00  
2   2018-02-27 13:00    2018-02-27 17:00

UnavailableTimes

ID  StartTime           EndTime  
2   2018-02-27 07:30    2018-02-27 08:30  
2   2018-02-27 08:00    2018-02-27 09:30  
2   2018-02-27 13:30    2018-02-27 14:00
2   2018-02-27 16:00    2018-02-27 18:00

I want to subtract the time ranges in UnavailableTimes from the time ranges in Schedule to get the available times like this:

AvailableTimes

ID  StartTime           EndTime  
2   2018-02-27 09:30    2018-02-27 12:00  
2   2018-02-27 13:00    2018-02-27 13:30
2   2018-02-27 14:00    2018-02-27 16:00  

EDIT: With the help from MJH I got the StartTimes and the EndTimes. Now I just need to put this together:

;WITH S1 AS (
        SELECT  U.EndTime AS StartTime
        FROM    UnavailableTimes U
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes W
                WHERE   U.EndTime BETWEEN W.StartTime AND DATEADD(s, -1, W.EndTime)
        )
        AND EXISTS (
                SELECT  *
                FROM    Schedule S
                WHERE   U.EndTime BETWEEN S.StartTime AND S.EndTime
        )
),
S2 AS (
        SELECT  S.StartTime
        FROM    Schedule S
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes U
                WHERE   S.StartTime BETWEEN U.StartTime AND U.EndTime
        )
)
SELECT  StartTime
FROM    S1
UNION ALL
SELECT  StartTime
FROM    S2
ORDER BY 1


;WITH S1 AS (
        SELECT  U.StartTime AS EndTime
        FROM    UnavailableTimes U
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes W
                WHERE   U.StartTime BETWEEN DATEADD(s, 1, W.StartTime) AND W.EndTime
        )
        AND EXISTS (
                SELECT  *
                FROM    Schedule S
                WHERE   U.StartTime BETWEEN S.StartTime AND S.EndTime
        )
),
S2 AS (
        SELECT  S.EndTime
        FROM    Schedule S
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes U
                WHERE   S.EndTime BETWEEN U.StartTime AND U.EndTime
        )
)
SELECT  EndTime
FROM    S1
UNION ALL
SELECT  EndTime
FROM    S2
ORDER BY 1

Answer 1

That's good, the final part of the puzzle is stitching it all together:

;WITH S1 AS (
        SELECT  U.EndTime AS StartTime
        FROM    UnavailableTimes U
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes W
                WHERE   U.EndTime BETWEEN W.StartTime AND DATEADD(s, -1, W.EndTime)
        )
        AND EXISTS (
                SELECT  *
                FROM    Schedule S
                WHERE   U.EndTime BETWEEN S.StartTime AND S.EndTime
        )
),
S2 AS (
        SELECT  S.StartTime
        FROM    Schedule S
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes U
                WHERE   S.StartTime BETWEEN U.StartTime AND U.EndTime
        )
),
S3 AS (
        SELECT  StartTime
        FROM    S1
        UNION ALL
        SELECT  StartTime
        FROM    S2
),
S AS (
        SELECT  StartTime, ROW_NUMBER() OVER(ORDER BY StartTime) RN
        FROM    S3
),
E1 AS (
        SELECT  U.StartTime AS EndTime
        FROM    UnavailableTimes U
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes W
                WHERE   U.StartTime BETWEEN DATEADD(s, 1, W.StartTime) AND W.EndTime
        )
        AND EXISTS (
                SELECT  *
                FROM    Schedule S
                WHERE   U.StartTime BETWEEN S.StartTime AND S.EndTime
        )
),
E2 AS (
        SELECT  S.EndTime
        FROM    Schedule S
        WHERE   NOT EXISTS (
                SELECT  *
                FROM    UnavailableTimes U
                WHERE   S.EndTime BETWEEN U.StartTime AND U.EndTime
        )
),
E3 AS (
        SELECT  EndTime
        FROM    E1
        UNION ALL
        SELECT  EndTime
        FROM    E2
),
E AS (
        SELECT  EndTime, ROW_NUMBER() OVER(ORDER BY EndTime) RN
        FROM    E3
)
SELECT  S.StartTime, E.EndTime
FROM    S INNER JOIN E
        ON S.RN = E.RN
ORDER BY S.RN