with tbl as (
select 1 n, to_date('01.01.2017', 'dd.mm.yyyy') s, to_date('10.01.2017', 'dd.mm.yyyy') e from dual union all
select 2 n, to_date('05.01.2017', 'dd.mm.yyyy') s, to_date('15.01.2017', 'dd.mm.yyyy') e from dual union all
select 3 n, to_date('11.01.2017', 'dd.mm.yyyy') s, to_date('17.01.2017', 'dd.mm.yyyy') e from dual union all
select 4 n, to_date('01.02.2017', 'dd.mm.yyyy') s, to_date('10.02.2017', 'dd.mm.yyyy') e from dual union all
select 5 n, to_date('05.02.2017', 'dd.mm.yyyy') s, to_date('15.02.2017', 'dd.mm.yyyy') e from dual union all
select 6 n, to_date('11.02.2017', 'dd.mm.yyyy') s, to_date('17.02.2017', 'dd.mm.yyyy') e from dual)
select distinct CONNECT_BY_ROOT s s, max(e) over (partition by CONNECT_BY_ROOT s) e from tbl t1
start with not exists (select 1 from tbl t2 where t1.s between t2.s and t2.e and t1.n <> t2.n)
connect by prior n <> n and prior e >= s and prior e <= e;
S E
----------- -----------
01.01.2017 17.01.2017
01.02.2017 17.02.2017
среда, 14 июня 2017 г.
How to select full periods
Подписаться на:
Комментарии к сообщению (Atom)
Комментариев нет:
Отправить комментарий