Вычитание диапазонов даты и времени из других диапазонов даты и времени (T-SQL)

Question

Мне нужна помощь для создания запроса SQL. По крайней мере, подсказка о том, как это сделать. У меня есть таблица Расписание с диапазонами даты и времени, а другая таблица UnavailableTimes также с диапазонами даты и времени. Отсюда я хочу доступное время, вычитая недоступное время из графика.

T-SQL (тестовые данные)

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

График

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

Я хочу вычесть временные диапазоны в UnavailableTimes из временных диапазонов в расписании, чтобы получить доступное время следующим образом:

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  

РЕДАКТИРОВАТЬ: С помощью MJH я получил StartTimes и EndTimes. Теперь мне просто нужно собрать это вместе:

;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

Это хорошо, последняя часть головоломки соединяет все вместе:

;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

Answer 2

У меня был трек в стартовые времена для вас, вы должны быть в состоянии решить, как сделать EndTimes самостоятельно:

;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