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 41
|
with liste_date as (
select 'deb' as flag, registre, date_debut as dte, date_fin as fin_courant
from t
union all
select 'fin' as flag, registre, date_fin as dte, NULL
from t
),
resultat as (
select flag
, registre
, fin_courant
, dte as date_debut
, case when flag = 'deb'
and lead(flag) over (partition by registre order by dte) = 'deb'
then lead(dte) over (partition by registre order by dte) - 1
when fin_courant > lead(dte) over (partition by registre order by dte)
then lead(dte) over (partition by registre order by dte) - 1
when flag = 'fin'
and lead(flag) over (partition by registre order by dte) = 'fin'
then lead(dte) over (partition by registre order by dte)
else fin_courant
end as date_fin
from liste_date
)
select registre, date_debut, date_fin
from resultat
where date_fin is not null
order by registre, date_debut
REGISTRE DATE_DEBUT DATE_FIN
---------- ------------------- -------------------
2 28/02/1987 00:00:00 27/02/1988 00:00:00
2 28/02/1988 00:00:00 27/02/1989 00:00:00
2 28/02/1989 00:00:00 27/02/1990 00:00:00
3 28/02/1987 00:00:00 09/02/1988 00:00:00
3 10/02/1988 00:00:00 26/02/1988 00:00:00
3 27/02/1988 00:00:00 27/02/1989 00:00:00
3 28/02/1989 00:00:00 27/02/1990 00:00:00
7 lignes sélectionnées |
Partager