1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| with t as (
select 1 as pat, 20160101 as deb, 20160106 as fin from dual union all
select 1, 20160108, 20160110 from dual union all
select 1, 20160111, 20160115 from dual union all
select 1, 20160201, 20160210 from dual union all
select 1, 20160301, 20160310 from dual union all
select 1, 20160311, 20160315 from dual union all
select 2, 20160501, 20160504 from dual union all
select 2, 20160506, 20160510 from dual
),
detecte_groupe as (
select pat, deb, fin
, case when lag(deb) over(partition by pat order by deb) is null then 1
when deb - lag(fin,1,deb) over(partition by pat order by deb) > 3 then 1
else 0
end as flag_grp
from t
),
regroupe as (
select pat, deb, fin
, sum(flag_grp) over(partition by pat order by deb) as grp
from detecte_groupe d
)
select pat, min(deb) as min_deb, max(fin) as max_deb
from regroupe
group by pat, grp
order by pat, min_deb |
Partager