Fonction analytique demandée

**hichamy** · 18/09/2008, 16h56

Bonjour a tous

J’ai une table source qui contient des dates continue avec des troux

Le but c’est de faire un regroupement par date manquante et Contrat qui change

Matricule Date_debut Contrat

001 01/01/2008 001
001 02/01/2008 001
001 04/01/2008 001
001 07/02/2008 002
001 08/02/2008 002
001 09/02/2008 002

Le but c’est de faire une rupture par date manquante et par contrat

Resultat

Matricule Date debut Date fin Contrat

001 01/01/2008 02/01/2008 001 car 03/01/2008 n’existe pas
001 04/01/2008 04/01/2008 001 Car changement de contrat
001 07/02/2008 09/02/2008 002

Je n’ai pas le droit d’utiliser le PL

Merci beaucoup de votre aide

Scorpi0 · 19/09/2008, 11h55

Bonjour,

A quelque cacahuètes près, c'est comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select date_veille, date_courant, Contrat_courant
from
(
  select   lag(Date_debut, 1) over(partition by Matricule order by Date_debut) date_veille,
           Date_debut date_courant,
           Contrat Contrat_courant
           lag(Contrat , 1) over(partition by Matricule order by Date_debut) Contrat_veille
  from    Source
)
where date_veille - date_courant > 1
or Contrat_veille != Contrat_courant

Sous requête : récupération des dates et des numéro de contrat de la ligne courante et de la ligne d'avant.

Sur requête : filtre sur les dates (différence supérieur à 1) ou rupture de contrat.

**salim11** · 19/09/2008, 12h58

Salut,
Quelle est ta version?

Voici une solution avec la 10g

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
 
SQL> select * from source order by matricule,contrat,date_debut;
 
MATRICULE  DATE_DEBU CON
---------- --------- ---
001        01-JAN-08 001
001        02-JAN-08 001
001        04-JAN-08 001
001        07-JAN-08 002
001        08-JAN-08 002
001        09-JAN-08 002
001        11-JAN-08 002
 
7 rows selected.
 
SQL> SELECT MATRICULE, DATE_DEBUT, CHAINE AS DATE_FIN ,CONTRAT
  2  FROM   (SELECT   CONTRAT, MATRICULE,DATE_DEBUT,NVL(DATE_DEBUT- LAG(DATE_DEBUT, 1) OVER(PARTITIO
N BY MATRICULE,CONTRAT ORDER BY DATE_DEBUT),1) IND           
  3    FROM    source ) T
  4  MODEL
  5    RETURN UPDATED ROWS
  6   PARTITION BY ( CONTRAT, MATRICULE ,IND)
  7    DIMENSION BY ( ROW_NUMBER() OVER (PARTITION BY MATRICULE,CONTRAT,IND ORDER BY
  8  DATE_DEBUT ASC) AS POSITION )
  9    MEASURES     (DATE_DEBUT, CAST( DATE_DEBUT AS VARCHAR2(50)) AS
 10  CHAINE)  IGNORE NAV
 11    RULES
 12      UPSERT
 13      ITERATE( 100)
 14        UNTIL ( PRESENTV(CHAINE[ITERATION_NUMBER+2],1,0) = 0)
 15      (
 16  CHAINE[1] = CHAINE[ ITERATION_NUMBER+1]
 17   )
 18  ORDER BY MATRICULE,CONTRAT
 19  /
 
MATRICULE  DATE_DEBU DATE_FIN                                           CON
---------- --------- -------------------------------------------------- ---
001        01-JAN-08 02-JAN-08                                          001
001        04-JAN-08 04-JAN-08                                          001
001        07-JAN-08 09-JAN-08                                          002
001        11-JAN-08 11-JAN-08                                          002
 
SQL>

Est ce que tu as une seule date manquante par contrat ?

**salim11** · 22/09/2008, 11h39

Envoyé par salim11

Est ce que tu as une seule date manquante par contrat ?

**salim11** · 29/09/2008, 17h19

Solution plus complète ( même si on a plusieurs dates manquantes)

Script

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
 
SELECT MATRICULE, DATE_DEBUT, CHAINE AS DATE_FIN ,CONTRAT
    FROM   (
SELECT contrat, matricule, 
       MAX (opt) OVER (PARTITION BY matricule, contrat ORDER BY date_debut)
                                                                          grp,
       date_debut
  FROM (SELECT contrat, matricule, ind, date_debut,
               CASE
                  WHEN ind >= 2
                     THEN   MAX (ind) OVER (PARTITION BY matricule, contrat ORDER BY date_debut)
                          + ROW_NUMBER () OVER (PARTITION BY matricule, contrat ORDER BY date_debut)
                  ELSE ind
               END opt
          FROM (SELECT contrat, matricule, date_debut,
                       NVL
                          (  date_debut
                           - LAG (date_debut, 1) OVER (PARTITION BY matricule, contrat ORDER BY date_debut),
                           1
                          ) ind
                  FROM SOURCE)) ) T
    MODEL
      RETURN UPDATED ROWS
    PARTITION BY ( CONTRAT, MATRICULE ,grp)
      DIMENSION BY ( ROW_NUMBER() OVER (PARTITION BY MATRICULE,CONTRAT,grp ORDER BY
    DATE_DEBUT ASC) AS POSITION )
      MEASURES     (DATE_DEBUT, CAST( DATE_DEBUT AS VARCHAR2(12)) AS
   CHAINE)  IGNORE NAV
     RULES
       UPSERT
       ITERATE( 100)
         UNTIL ( PRESENTV(CHAINE[ITERATION_NUMBER+2],1,0) = 0)
       (
   CHAINE[1] = CHAINE[ ITERATION_NUMBER+1]
    )
   ORDER BY MATRICULE,CONTRAT,date_debut

Démo:

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
SQL> SELECT * FROM source ORDER BY matricule,contrat,date_debut;
 
MATRICULE  CONTRAT    DATE_DEBUT
---------- ---------- ----------
001        001        2008-01-01
001        001        2008-01-02
001        001        2008-01-04
001        001        2008-01-05
001        001        2008-01-07
001        001        2008-01-09
001        001        2008-01-10
001        002        2008-01-07
001        002        2008-01-08
001        002        2008-01-09
001        002        2008-01-11
 
MATRICULE  CONTRAT    DATE_DEBUT
---------- ---------- ----------
001        002        2008-01-12
001        002        2008-01-14
001        002        2008-01-15
001        002        2008-01-18
001        002        2008-01-19
001        002        2008-01-20
001        002        2008-01-22
 
18 ligne(s) sélectionnée(s).
 
SQL> SELECT MATRICULE, DATE_DEBUT, CHAINE AS DATE_FIN ,CONTRAT
  2      FROM   (
  3  SELECT contrat, matricule, 
  4         MAX (opt) OVER (PARTITION BY matricule, contrat ORDER BY date_debut)
  5                                                                            grp,
  6         date_debut
  7    FROM (SELECT contrat, matricule, ind, date_debut,
  8                 CASE
  9                    WHEN ind >= 2
 10                       THEN   MAX (ind) OVER (PARTITION BY matricule, contrat ORDER BY date_debut
)
 11                            + ROW_NUMBER () OVER (PARTITION BY matricule, contrat ORDER BY date_d
ebut)
 12                    ELSE ind
 13                 END opt
 14            FROM (SELECT contrat, matricule, date_debut,
 15                         NVL
 16                            (  date_debut
 17                             - LAG (date_debut, 1) OVER (PARTITION BY matricule, contrat ORDER BY
 date_debut),
 18                             1
 19                            ) ind
 20                    FROM SOURCE)) ) T
 21      MODEL
 22        RETURN UPDATED ROWS
 23      PARTITION BY ( CONTRAT, MATRICULE ,grp)
 24        DIMENSION BY ( ROW_NUMBER() OVER (PARTITION BY MATRICULE,CONTRAT,grp ORDER BY
 25      DATE_DEBUT ASC) AS POSITION )
 26        MEASURES     (DATE_DEBUT, CAST( DATE_DEBUT AS VARCHAR2(12)) AS
 27     CHAINE)  IGNORE NAV
 28       RULES
 29         UPSERT
 30         ITERATE( 100)
 31           UNTIL ( PRESENTV(CHAINE[ITERATION_NUMBER+2],1,0) = 0)
 32         (
 33     CHAINE[1] = CHAINE[ ITERATION_NUMBER+1]
 34      )
 35     ORDER BY MATRICULE,CONTRAT,date_debut
 36   
SQL> /
 
MATRICULE  DATE_DEBUT DATE_FIN     CONTRAT
---------- ---------- ------------ ----------
001        2008-01-01 2008-01-02   001
001        2008-01-04 2008-01-05   001
001        2008-01-07 2008-01-07   001
001        2008-01-09 2008-01-10   001
001        2008-01-07 2008-01-09   002
001        2008-01-11 2008-01-12   002
001        2008-01-14 2008-01-15   002
001        2008-01-18 2008-01-20   002
001        2008-01-22 2008-01-22   002
 
9 ligne(s) sélectionnée(s).
 
SQL>