Исключить пересекающиеся временные промежутки sql

Question

Есть 2 таблицы: t1 содержит основной временной промежуток, t2 - временные промежутки внутри него, которые необходимо исключить.

CREATE TABLE t1 (
    start_date DATE NOT NULL,
    finish_date DATE NOT NULL
);

INSERT INTO t1 (start_date, finish_date)
VALUES ('2023-04-02', '2023-07-31');


CREATE TABLE t2 (
    exception_start_date DATE NOT NULL,
    exception_finish_date DATE NOT NULL
);

INSERT INTO t2 (exception_start_date, exception_finish_date)
VALUES 
('2023-04-15', '2023-05-01'),
--('2023-06-01', '2023-07-01');

Временные промежутки из t2 всегда целиком лежат внутри t1 и не пересекаются друг с другом. Их может быть неограниченное количество

Если исключение только одно, решение простое и может выглядеть так:

with cte as (
select *
from t1 join t2
on t1.start_date < t2.exception_start_date and t1.finish_date > t2.exception_finish_date
)
select start_date as start_date, dateadd(day, -1, exception_start_date) as finish_date
from cte
union all
select dateadd(day, 1, exception_finish_date) as start_dt, finish_date as finish_date
from cte

Подскажите, пожалуйста, как реализовать ту же логику, но для неограниченного кол-ва исключений внутри одного интервала?

p.s. Синтаксис любой СУБД

Answer 1

В общем, задача решена, написалось что-то такое

with internal_border_exception as
(
select *
from t1 join t2 on t1.start_date < t2.exception_start_date and t1.finish_date > t2.exception_finish_date
)
, final_cte as (
    -- обойдём все участки "смотря слева"
    select 
       
        case 
            when lag(exception_finish_date) over (partition by  start_date order by exception_start_date) is null then start_date
            else dateadd(day, 1, lag(exception_finish_date) over (partition by start_date order by exception_start_date)) 
        end as start_date,
        dateadd(day, -1, exception_start_date) as finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is not null
    union all
    -- обработаем "крайней правый" промежуток
    select 
        case 
            when lead(exception_start_date) over (partition by  start_date order by exception_start_date) is not null then null 
            else dateadd(day, 1, exception_finish_date) 
        end as start_date,
        case 
            when lead(exception_start_date) over (partition by start_date order by exception_start_date) is not null then null 
            else finish_date 
        end as finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is not null
    union all
    -- выведем интервалы, где не было внутренних исключений
    select 
        start_date, 
        finish_date
    from 
        internal_border_exception
    where 
        exception_start_date is null
)

select  start_date, finish_date
from final_cte
where start_date is not null
order by start_date;

БЛОГ НА HUSL

Исключить пересекающиеся временные промежутки sql

Ответы (1 шт):