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 28 29 30 31 32 33 34 35 36 37 38 39 40
| With MaTable as
(
select 1 as id, 1 as ida, trunc(sysdate) as deb, trunc(sysdate) + 1 as fin from dual union all
select 2 , 1 , trunc(sysdate) + 1 , trunc(sysdate) + 2 from dual union all
select 3 , 1 , trunc(sysdate) + 2 , trunc(sysdate) + 3 from dual union all
select 4 , 1 , trunc(sysdate) + 3 , trunc(sysdate) + 4 from dual union all
select 5 , 1 , trunc(sysdate) + 4 , trunc(sysdate) + 5 from dual union all
select 6 , 1 , trunc(sysdate) + 5 , trunc(sysdate) + 6 from dual union all
select 7 , 1 , trunc(sysdate) + 6 , trunc(sysdate) + 7 from dual union all
select 8 , 1 , trunc(sysdate) + 7 , trunc(sysdate) + 8 from dual union all
select 9 , 1 , trunc(sysdate) + 8 , trunc(sysdate) + 9 from dual union all
select 10 , 1 , trunc(sysdate) + 100 , trunc(sysdate) + 101 from dual union all
select 11 , 1 , trunc(sysdate) + 101 , trunc(sysdate) + 102 from dual union all
select 12 , 1 , trunc(sysdate) + 102 , trunc(sysdate) + 103 from dual
)
, sr1 as
(
select id, ida, deb, fin,
case deb
when lag(fin) over(partition by ida order by id asc)
then null
else id
end as dlg
from MaTable
)
, sr2 as
(
select id, ida, deb, fin,
last_value(dlg ignore nulls) over(partition by ida order by id asc) as lvl
from sr1
)
select ida, min(deb) as deb, max(fin) as fin
from sr2
group by ida, lvl
order by ida asc, deb asc;
IDA DEB FIN
---------- ---------- ----------
1 16/03/2011 25/03/2011
1 24/06/2011 27/06/2011 |
Partager