Главная > SQL > Объединение интервалов дат


Объединение интервалов дат

with t as (
select to_date ('01.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('10.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('03.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('05.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('10.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('12.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('13.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('20.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('01.02.2009', 'dd.mm.yyyy') beg_date,
       to_date ('10.02.2009', 'dd.mm.yyyy') end_date
 from dual
)
--
select min (beg_date) as beg_date,
       max (end_date) as end_date
from (
      select beg_date,
             end_date,
             sum(strt_grp) over (order by beg_date, end_date ) grp_num
      from (
             select  beg_date,
                     end_date,
                     case when beg_date > 1 + max(end_date) over (order by beg_date, end_date rows between unbounded preceding and 1 preceding) then 1 end strt_grp
             from t
           )
     )
group by grp_num
order by beg_date;

BEG_DATE    END_DATE
----------- -----------
01-01-2009  20-01-2009
01-02-2009  10-02-2009

Похожие записи:

  1. Пока что нет комментариев.
  1. Пока что нет уведомлений.