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 42 43 44 45 46 47 48 49 50
| WITH tb_contrats AS (
SELECT '000210' matricule, to_date('10/01/2000','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
SELECT '000340' matricule, to_date('10/07/2003','dd/mm/yyyy') deb, to_date('31/12/2035','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual Union ALL
SELECT '004580' matricule, to_date('01/01/2010','dd/mm/yyyy') deb, to_date('15/02/2010','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
SELECT '004580' matricule, to_date('01/06/2010','dd/mm/yyyy') deb, to_date('30/05/2011','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
SELECT 'test' matricule, to_date('01/10/2012','dd/mm/yyyy') deb, to_date('01/11/2012','dd/mm/yyyy') fin, 'CDD' type_contrat FROM Dual Union ALL
SELECT 'test' matricule, to_date('02/11/2012','dd/mm/yyyy') deb, to_date('31/12/2012','dd/mm/yyyy') fin, 'CDI' type_contrat FROM Dual
),
tb_suspensions AS (
SELECT '000210' matricule, to_date('18/01/2012','dd/mm/yyyy') deb, to_date('17/06/2014','dd/mm/yyyy') fin FROM Dual Union ALL
SELECT '004580' matricule, to_date('12/10/2010','dd/mm/yyyy') deb, to_date('30/11/2010','dd/mm/yyyy') fin FROM Dual Union ALL
SELECT 'test' matricule, to_date('08/10/2012','dd/mm/yyyy') deb, to_date('10/10/2012','dd/mm/yyyy') fin FROM Dual Union ALL
SELECT 'test' matricule, to_date('24/10/2012','dd/mm/yyyy') deb, to_date('26/10/2012','dd/mm/yyyy') fin FROM Dual Union ALL
SELECT 'test' matricule, to_date('07/11/2012','dd/mm/yyyy') deb, to_date('10/11/2012','dd/mm/yyyy') fin FROM Dual Union ALL
SELECT 'test' matricule, to_date('13/11/2012','dd/mm/yyyy') deb, to_date('20/11/2012','dd/mm/yyyy') fin FROM Dual
),
t_union as (
select matricule, deb as dte, type_contrat, 'deb' as type_date, 'contrat' as src
from tb_contrats
union all
select matricule, fin , type_contrat, 'fin' , 'contrat'
from tb_contrats
union all
select matricule, deb , NULL , 'deb' , 'suspension'
from tb_suspensions
union all
select matricule, fin , NULL , 'fin' , 'suspension'
from tb_suspensions
),
res as (
select matricule
, dte as date_deb
, case when type_date = 'fin' and src = 'contrat'
then NULL
else lead(dte) over (partition by matricule order by dte)
end as date_fin
, (select max(type_contrat) keep (dense_rank first order by dte desc)
from t_union t2
where t2.matricule = t.matricule
and t2.dte <= t.dte
and t2.src = 'contrat'
and t2.type_date = 'deb') || case when type_date = 'deb' and src = 'suspension'
then ' SUSPENSION'
else NULL
end as type_contrat
from t_union t
)
select *
from res
where date_fin is not null |
Partager