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
| with cte_matable (identifiant, date_debut, date_fin, valeur1) as
(
select 'toto', date '2011-11-12', date '2012-11-12', 300 from dual union all
select 'toto', date '2012-11-13', date '2012-11-15', 350 from dual union all
select 'toto', date '2012-11-16', date '2014-11-15', 350 from dual union all
select 'toto', date '2014-11-16', date '2015-11-15', 350 from dual union all
select 'toto', date '2015-11-16', date '2015-12-17', 400 from dual union all
select 'toto', date '2015-12-18', date '2016-11-12', 300 from dual
)
, cte_tmp (identifiant, date_debut, date_fin, valeur1, grp) as
(
select identifiant, date_debut, date_fin, valeur1
, row_number() over(partition by identifiant order by date_debut asc)
- row_number() over(partition by identifiant, valeur1 order by date_debut asc)
from cte_matable
)
select identifiant
, min(date_debut) as date_debut
, max(date_fin) as date_fin
, valeur1
from cte_tmp
group by identifiant
, valeur1
, grp
order by identifiant asc
, date_debut asc;
IDENTIFIANT DATE_DEBUT DATE_FIN VALEUR1
----------- ---------- ---------- -------
toto 2011-11-12 2012-11-12 300
toto 2012-11-13 2015-11-15 350
toto 2015-11-16 2015-12-17 400
toto 2015-12-18 2016-11-12 300 |
Partager