Décomposer des périodes

**milka** · 19/10/2012, 17h59

Bonjour,

Je cherche la requête sql qui me permettrait de décomposer une période en plusieurs sous-périodes en fonction des évènements périodiques d'une autre table.
Exemple : décomposition d'un contrat en fonction des périodes de suspensions éventuelles.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
with
tb_contrats as
(
select '000210' matricule, '10/01/2000' deb, '31/12/2035' fin, 'CDI' type_contrat FROM Dual Union all
select '000340' matricule, '10/07/2003' deb, '31/12/2035' fin, 'CDI' type_contrat FROM Dual Union all
select '004580' matricule, '01/01/2010' deb, '15/02/2010' fin, 'CDD' type_contrat FROM Dual Union all
select '004580' matricule, '01/06/2010' deb, '30/05/2011' fin, 'CDD' type_contrat FROM Dual
),
tb_suspensions as
(
select '000210' matricule, '18/01/2012' deb, '17/06/2014' fin FROM Dual Union all
select '004580' matricule, '12/10/2010' deb, '30/11/2010' fin FROM Dual
)

Résultat attendu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
MATRICULE DEB        FIN        TYPE_CONTRAT 
--------- ---------- ---------- ------------ 
000210    10/01/2000 17/01/2012 CDI          
000210    18/01/2012 17/06/2014 CDI SUSPENSION
000210    18/01/2014 31/12/2035 CDI          
000340    10/07/2003 31/12/2035 CDI          
004580    01/01/2010 15/02/2010 CDD          
004580    01/06/2010 11/10/2010 CDD          
004580    12/10/2010 30/11/2010 CDD SUSPENSION
004580    01/12/2010 30/05/2011 CDD

Merci d'avance pour votre aide précieuse.
Faut que je prenne des vitamines là ...

**skuatamad** · 20/10/2012, 01h45

Voici une approche, j'espère que c'est correcte et malgré tout performant.
J'ai considéré qu'il fallait récupérer pour les suspensions le dernier type_contrat en court côté tb_contrats (j'ai rajouté un test pour l'illustrer) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MATRIC DATE_DEB   DATE_FIN   TYPE_CONTRAT
------ ---------- ---------- --------------
000210 10/01/2000 18/01/2012 CDI
000210 18/01/2012 17/06/2014 CDI SUSPENSION
000210 17/06/2014 31/12/2035 CDI
000340 10/07/2003 31/12/2035 CDI
004580 01/01/2010 15/02/2010 CDD
004580 01/06/2010 12/10/2010 CDD
004580 12/10/2010 30/11/2010 CDD SUSPENSION
004580 30/11/2010 30/05/2011 CDD
test   01/10/2012 08/10/2012 CDD
test   08/10/2012 10/10/2012 CDD SUSPENSION
test   10/10/2012 24/10/2012 CDD
test   24/10/2012 26/10/2012 CDD SUSPENSION
test   26/10/2012 01/11/2012 CDD
test   02/11/2012 07/11/2012 CDI
test   07/11/2012 10/11/2012 CDI SUSPENSION
test   10/11/2012 13/11/2012 CDI
test   13/11/2012 20/11/2012 CDI SUSPENSION
test   20/11/2012 31/12/2012 CDI
 
18 rows selected.

Reste à gérer les date_deb et date_fin à afficher avec des +1 ou -1 car l'exemple de résultat proposé me semble inconsistent.

En cas de problème de perf, merci de préciser la gestion d'affichage du type_contrat, et dans la mesure du possible filtrer au maximum les tables lors de l'UNION.

**milka** · 22/10/2012, 13h29

Bonjour et merci pour la réponse.

C'est vrai que l'union est couteux car j'ai pas mal de données.
J'ai souvent ce travail de période à mettre en place dans des requêtes et je souhaitais trouver la solution la plus performante tout de suite pour l'implémenter ailleurs.

Si vous avez une autre idée d'algo ...

Merci

**skuatamad** · 22/10/2012, 14h22

Quelle version d'oracle ? si 11GR2, peut être avec un WITH recursif.
Sinon fonction pipelined ou vue matérialisée pour stocker le résultat de l'UNION.

Par ailleurs, quelle est la gestion d'affichage du type_contrat ? est ce qu'un même matricule peut passer de CDD à CDI ?

**milka** · 22/10/2012, 14h58

Dans le cas des contrats, il s'agit d'une 10gr2. J'ai d'autres bases en 11gr2.

Je ne comprends pas ta question au sujet de la "gestion d'affichage".
Mais oui, le même matricule peux passer de CDD à CDI

**Waldar** · 22/10/2012, 15h49

Je ne sais pas si ce sera plus performant que la solution de skuatamad, mais je vous donne une autre piste donc ça vaut peut-être le coup de l'essayer.

Implémentez ce calendrier, dès lors vous pouvez faire cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
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
)
  ,  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
)
  ,  SR as
(
    select ctr.matricule, cl1.cal_jour
         , ctr.type_contrat || case when ssp.matricule is not null then ' SUSPENSION' end as type_contrat
         , row_number() over(partition by ctr.matricule                                  order by cl1.cal_jour asc)
         - row_number() over(partition by ctr.matricule, ctr.type_contrat, ssp.matricule order by cl1.cal_jour asc) as grp
      from tb_contrats ctr partition by (ctr.matricule)
right join calendrier cl1 
        on cl1.cal_jour between ctr.deb and ctr.fin
 left join tb_suspensions ssp
inner join calendrier cl2
        on cl2.cal_jour between ssp.deb and ssp.fin
        on ssp.matricule = ctr.matricule
       and cl2.cal_jour  = cl1.cal_jour
)
  select matricule
       , min(cal_jour) as date_deb
       , max(cal_jour) as date_fin
       , type_contrat
    from SR
   where type_contrat is not null
group by matricule
       , type_contrat
       , grp
order by matricule     asc
       , min(cal_jour) asc;
 
MATRICULE DATE_DEB   DATE_FIN   TYPE_CONTRAT 
--------- ---------- ---------- --------------
000210    10/01/2000 17/01/2012 CDI          
000210    18/01/2012 17/06/2014 CDI SUSPENSION
000210    18/01/2014 31/12/2035 CDI          
000340    10/07/2003 31/12/2035 CDI          
004580    01/01/2010 15/02/2010 CDD          
004580    01/06/2010 11/10/2010 CDD          
004580    12/10/2010 30/11/2010 CDD SUSPENSION
004580    01/12/2010 30/05/2011 CDD

**pacmann** · 23/10/2012, 16h25

Salut !

J'ai voulu essayer aussi pour rigoler :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
51
52
53
54
 
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
)
  ,  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
), t_res_temp as (
select tc.matricule, tc.deb, tc.fin, type_contrat, ts.deb as debs, ts.fin as fins
     , case when lag(ts.fin, 1) over(partition by tc.matricule order by ts.deb) between tc.deb and tc.fin then 
       lag(ts.fin, 1) over(partition by tc.matricule order by ts.deb) end finsp
     , case when lead(ts.deb, 1) over(partition by tc.matricule order by ts.deb) between tc.deb and tc.fin then 
       lead(ts.deb, 1) over(partition by tc.matricule order by ts.deb)  end debsn 
from tb_contrats tc
  left outer join tb_suspensions ts on tc.matricule = ts.matricule
    and ts.fin between tc.deb and tc.fin                  
  )
  , t_res as (
select t_res_temp.*, case when fins is null then 'nosus' 
                          when fins is not null and finsp is not null and debsn is not null then 'midsus'
                          when fins is not null and finsp is null and debsn is not null then 'firstsus'
                          when fins is not null and finsp is not null and debsn is null then 'lastsus'
                          else 'uniqsus' end nseg
from t_res_temp
)
select matricule, case when n = 1 and nseg in ('nosus', 'firstsus', 'uniqsus') then deb
                       when n = 1 and nseg in ('lastsus', 'midsus') then debs                       
                       when n = 2 and nseg in ('firstsus', 'uniqsus') then debs
                       when n = 2 and nseg in ('lastsus', 'midsus') then fins + 1                                               
                       when n = 3 then fins + 1 end debr                       
                , case when n = 1 and nseg = 'nosus' then fin
                       when n = 1 and nseg in ('firstsus', 'uniqsus') then debs - 1 
                       when n = 1 and nseg in ('lastsus', 'midsus') then fins                       
                       when n = 2 and nseg in ('firstsus', 'uniqsus') then fins
                       when n = 2 and nseg = 'lastsus' then fin
                       when n = 2 and nseg = 'midsus' then debsn - 1                                               
                       when n = 3 and nseg = 'firstsus' then debsn - 1 
                       when n = 3 and nseg = 'uniqsus' then fin end finr 
                , type_contrat
                , case when n = 1 and nseg in ('midsus', 'lastsus') or n = 2 and nseg in ('uniqsus', 'firstsus')                        
                  then 'SUSPENSION' end susps
                --, deb, fin, debs, fins, debsn, finsp, n, nseg
from t_res
  join (select level n 
        from dual 
         connect by level <= 3) nums on nums.n <= case when nseg in ('nosus') then 1 
                                                       when nseg in ('midsus', 'lastsus') then 2
                                                       when nseg in ('uniqsus', 'firstsus') then 3 end 
order by matricule, deb, debs, n

L'idée c'est d'y aller à la jointure externe entre contrats et suspensions, puis faire une jointure avec max 3 lignes (découpage de la période provoquée par la période de suspension).

Le code est dégueulasse, désolé j'ai un peu galéré, ça fait longtemps que j'ai plus fait de SQL

Côté performances, il faut voir quel est le besoin précis. S'il s'agit de sortir ce genre de rapport pour un numéro de matricule précis, il faut modifier la requête pour l'intégrer aux bons endroits...

**milka** · 24/10/2012, 18h18

Waldar,

merci pour ta réponse.
J'étais partie sur cette idée mais perfs désastreuses.

Pour améliorer les perfs, je pensais à décomposer le contrat en dates uniquement lorsqu'il y a une suspension pendant le contrat. Mais je ne vois pas comment faire. Une idée ?

**skuatamad** · 24/10/2012, 20h37

Je ne suis pas rentrée dans le détail des solutions proposées par Waldar et pacmann, mais si les perfs sont désastreuse, d'abord analyser pourquoi (plan, trace étendue...)

Ensuite est ce vraiment nécessaire de travailler sur toutes les années et tous les contrats ?

Enfin pour ma requête j'utiliserais volontier une vue matérialisée (voir peut être matérialisée sur une IOT) pour les UNION, ce qui permettrait d'avoir une structure plus simple à interroger pour ce besoin.

Décomposer des périodes

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager