PL/SQL Regroupement de date

Version imprimable

29/01/2015, 16h01
malus56

PL/SQL Regroupement de date

Bonjour,

Je travaille sur une table POSTE[Id, nom, dateDebut, DateFin] et une table SITUATION[Id, dateDebSit,DateFinSit, id_Poste]
J'ai 2 paramètres Début et Fin et je dois recuperer pour chaque poste les intervalles de dates ou le poste sera inoccupée.

C'est à dire:

Poste:
1 poste1 01/01/2014 31/12/2014

Affectation
1 01/02/2014 31/02/2014 1
2 20/02/2014 20/03/2014 1
3 01/06/2014 31/06/2014 1

Je veux donc récupérer a la fin
Poste DateDeb DateFin
Poste1 01/01/2014 01/02/2014
Poste1 20/03/2014 01/06/2014
Poste1 01/07/2014 31/22/2014

Je ne parviens pas a faire la requête ou procédure qui me permettra de récupérer ce résultat, pouvez vous m'aider à construire ma requête.
Merci

Il vous faut une table calendrier.
Ensuite il faut passer par la méthode de Tabibitosan.

Code:

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 poste (id_poste, nom, datedebut, datefin) as
(
select 1, 'poste1', date '2014-01-01', date '2014-12-31' from dual
)
  ,  situation (id_sit, datedebsit, datefinsit, id_poste) as
(
select 1, date '2014-02-01', date '2014-02-28', 1 from dual union all
select 2, date '2014-02-20', date '2014-03-20', 1 from dual union all
select 3, date '2014-06-01', date '2014-06-30', 1 from dual
)
  , calendrier (id_poste, cal_jour) as
(
    select id_poste, datedebut + level - 1
      from poste
connect by level <= datefin - datedebut + 1
)
  ,  ident (id_poste, cal_jour, attribue) as
(
select cal.id_poste, cal.cal_jour
     , case
         when exists (select null
                        from situation sit
                       where sit.id_poste = cal.id_poste
                         and cal.cal_jour between sit.datedebsit and sit.datefinsit)
         then 1
         else 0
       end
  from calendrier cal
)
  , regroup (id_poste, cal_jour, attribue, grp) as
(
select id_poste, cal_jour, attribue
     , row_number() over(                      order by cal_jour asc)
     - row_number() over(partition by attribue order by cal_jour asc) 
  from ident
)
  select id_poste
       , min(cal_jour) as jour_deb
       , max(cal_jour) as jour_fin
    from regroup
   where attribue = 0
group by id_poste, grp
order by id_poste, jour_deb;
 
  ID_POSTE JOUR_DEB            JOUR_FIN          
---------- ------------------- -------------------
         1 2014-01-01 00:00:00 2014-01-31 00:00:00 
         1 2014-03-21 00:00:00 2014-05-31 00:00:00 
         1 2014-07-01 00:00:00 2014-12-31 00:00:00

Le when exists dans le case n'est pas super élégant cela dit.

Ton example fonctionne mais lorsque j'essaie de l'appliquer à mon exemple particulier la requête se lance mais tourne comme si il y avait une boucle infini

Code:

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
With 
poste(id_poste, nom, datedebut, datefin) AS
(
  select 
  CA2IPOSITION.SYSID,
  CA2IPOSITION.CA2ITITLE,
  CA2IPOSITION.CA2IACTUALSTARTDATE,
  CA2IPOSITION.CA2IACTUALENDDATE
  from CA2IPOSITION
),
Situation(datedebsit, datefinsit, id_poste) AS
(
  Select 
  PSALSIT.SA_DATDEBSIT,
  PSALSIT.SA_DATFINSIT,
  PSALSIT.CA2IPOSTEPRINCIPAL_ID
  from PSALSIT
 
),
calendrier (id_poste, cal_jour) as
(
    select 
    id_poste,
    datedebut + level - 1
    from poste
    connect by level <= datefin - datedebut + 1
),
ident (id_poste, cal_jour, attribue) as
(
    select
    cal.id_poste,
    cal.cal_jour
     , case
         when exists (select null
                        from situation sit
                       where sit.id_poste = cal.id_poste
                         and cal.cal_jour between sit.datedebsit and sit.datefinsit)
         then 1
         else 0
       end
  from calendrier cal
)
, regroup (id_poste, cal_jour, attribue, grp) as
(
    select id_poste, cal_jour, attribue
         , row_number() over(                      order by cal_jour asc)
         - row_number() over(partition by attribue order by cal_jour asc) 
      from ident
)
  select id_poste
       , min(cal_jour) as jour_deb
       , max(cal_jour) as jour_fin
    from regroup
   where attribue = 0
group by id_poste, grp
order by id_poste, jour_deb;

Ou es-ce que j'ai fait une erreur?
Merci de ton aide.

Oui il y a une sorte de boucle infinie (enfin peut être pas infinie mais très très très grande en fonction de la taille de la table poste et des bornes).
Avec une ligne le simple CONNECT BY fonctionne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> with poste (id_poste, nom, datedebut, datefin) as
  2  (
  3  select 1, 'poste1', date '2014-01-01', date '2014-01-05' from dual
  4  ),
  5   calendrier (id_poste, cal_jour) as
  6  (
  7      select id_poste, datedebut + level - 1
  8        from poste
  9  connect by level <= datefin - datedebut + 1
 10  )
 11  select * from calendrier;
 
  ID_POSTE CAL_JOUR
---------- --------
         1 01/01/14
         1 02/01/14
         1 03/01/14
         1 04/01/14
         1 05/01/14

Mais avec 2 lignes dans poste on obtient :

Code:

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
86
87
88
89
90
91
92
93
94
95
96
SQL> with poste (id_poste, nom, datedebut, datefin) as
  2  (
  3  select 1, 'poste1', date '2014-01-01', date '2014-01-05' from dual union all
  4  select 2, 'poste2', date '2014-02-01', date '2014-02-05' from dual
  5  ),
  6   calendrier (id_poste, cal_jour) as
  7  (
  8      select id_poste, datedebut + level - 1
  9        from poste
 10  connect by level <= datefin - datedebut + 1
 11  )
 12  select * from calendrier;
 
  ID_POSTE CAL_JOUR
---------- --------
         1 01/01/14
         1 02/01/14
         1 03/01/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
         2 03/02/14
         1 04/01/14
 
  ID_POSTE CAL_JOUR
---------- --------
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
         2 02/02/14
         1 03/01/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
 
  ID_POSTE CAL_JOUR
---------- --------
         1 05/01/14
         2 05/02/14
         2 03/02/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
         2 01/02/14
         1 02/01/14
 
  ID_POSTE CAL_JOUR
---------- --------
         1 03/01/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
         2 03/02/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
 
  ID_POSTE CAL_JOUR
---------- --------
         2 04/02/14
         1 05/01/14
         2 05/02/14
         2 02/02/14
         1 03/01/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
 
  ID_POSTE CAL_JOUR
---------- --------
         2 03/02/14
         1 04/01/14
         1 05/01/14
         2 05/02/14
         2 04/02/14
         1 05/01/14
         2 05/02/14
 
62 rows selected.
 
SQL>

Ce qui ne convient pas du tout, avec seulement 2 lignes sur 5 jours...

Il faut utiliser une vrai table calendrier comme celle proposée par Waldar, ou alors générer le calendrier comme ceci :

Code:

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
SQL> with poste (id_poste, nom, datedebut, datefin) as
  2  (
  3  select 1, 'poste1', date '2014-01-01', date '2014-01-05' from dual union all
  4  select 2, 'poste2', date '2014-02-01', date '2014-02-05' from dual
  5  ),
  6   calendrier (id_poste, cal_jour) as
  7  (
  8      select id_poste, datedebut + level - 1
  9        from poste
 10  connect by level <= datefin - datedebut + 1
 11     AND PRIOR id_poste = id_poste
 12     AND PRIOR sys_guid() IS NOT NULL
 13  )
 14  select * from calendrier
 15  ;
 
  ID_POSTE CAL_JOUR
---------- --------
         1 01/01/14
         1 02/01/14
         1 03/01/14
         1 04/01/14
         1 05/01/14
         2 01/02/14
         2 02/02/14
         2 03/02/14
         2 04/02/14
         2 05/02/14
 
10 rows selected.
 
SQL>

Bonjour,

Jusqu'à la partie ident cela fonctionne

Code:

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
2c9e86034941ac2e014956fa2104290a	agent comptable junior	27/10/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	28/10/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	29/10/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	30/10/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	31/10/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	01/11/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	02/11/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	03/11/14	1
2c9e86034941ac2e014956fa2104290a	agent comptable junior	04/11/14	1
2c9e86034985c2e701498aaebff8730f	AI test	16/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	17/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	18/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	19/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	20/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	21/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	22/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	23/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	24/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	25/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	26/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	27/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	28/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	29/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	30/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	31/12/14	1
2c9e86034985c2e701498aaebff8730f	AI test	01/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	02/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	03/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	04/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	05/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	06/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	07/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	08/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	09/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	10/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	11/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	12/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	13/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	14/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	15/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	16/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	17/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	18/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	19/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	20/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	21/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	22/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	23/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	24/01/15	1
2c9e86034985c2e701498aaebff8730f	AI test	25/01/15	1

Mais dès que je tente la partie regroupement je me retrouve avec une liste vide

Code:

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
 
posteSit(idPoste,nom, debutSit, FinSit) AS
(
  select CA2IPOSITION.sysid, CA2IPOSITION.CA2ITITLE,SA_DATDEBSIT,NVL(SA_DATFINSIT, SA_DATDEBSIT)
  from CA2IPOSITION,psalsit
  where CA2IPOSITION.sysid=psalsit.CA2IPOSTEPRINCIPAL_ID
  and CA2IPOSITION.CA2ITITLE!='Poste test 3'----------------------------A supprimer
  ORDER BY  CA2IPOSITION.CA2ITITLE,SA_DATDEBSIT,SA_DATFINSIT
 
 
)
 ,
  calendrier (idposte,nom, cal_jour) as
(
    select idposte,nom, debutSit + level - 1
        from posteSit
   connect by level <= FinSit - debutSit +1
     AND PRIOR idposte = idposte
     AND PRIOR sys_guid() IS NOT NULL
 
)
,  ident (idposte,nom, cal_jour, attribue) as
(
select distinct cal.idposte,cal.nom, cal.cal_jour
     , case
         when exists (select null
                        from posteSit sit
                       where sit.idposte = cal.idposte
                         and cal.cal_jour between sit.debutSit and sit.FinSit)
         then 1
         else 0
       end
  from calendrier cal
  order by cal.nom, cal.cal_jour 
)
, regroup (idposte, cal_jour, attribue, grp) as
(
    select idposte, cal_jour, attribue
         , row_number() over(                      order by cal_jour asc)
         - row_number() over(partition by attribue order by cal_jour asc) 
      from ident
)
  select idposte
       , min(cal_jour) as jour_deb
       , max(cal_jour) as jour_fin
    from regroup
   where attribue = 0
group by idposte, grp
order by idposte, jour_deb;

Une idée de l'endroit ou je me trompe?
Merci de votre aide

Il faut rajouter l'id_poste dans les partition by :

Code:

1
2
3
4
5
6
7
, regroup (idposte, cal_jour, attribue, grp) as
(
    select idposte, cal_jour, attribue
         , row_number() over(partition by id_poste           order by cal_jour asc)
         - row_number() over(partition by id_poste, attribue order by cal_jour asc) 
      from ident
)

sans calndrier

Code:

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
 
DROP TABLE Poste;
CREATE TABLE POSTE
  (
    Id        NUMBER(3) PRIMARY KEY,
    nom       VARCHAR2(35),
    dateDebut DATE,
    DateFin   DATE
  );
DROP TABLE situation;
CREATE TABLE SITUATION
  (
    Id           NUMBER(3) PRIMARY KEY,
    dateDebutSit DATE,
    DateFinSit   DATE,
    idPoste      NUMBER
  );
INSERT
INTO POSTE VALUES
  (
    1 ,
    'POSTE1' ,
    to_date('01/01/2014','DD/MM/YYYY'),
    to_date('31/12/2014','DD/MM/YYYY')
  );
INSERT
INTO POSTE VALUES
  (
    2 ,
    'POSTE2' ,
    to_date('01/01/2013','DD/MM/YYYY'),
    to_date('31/12/2014','DD/MM/YYYY')
  );
INSERT
INTO SITUATION
  (SELECT 1,
      to_date('01/02/2014','DD/MM/YYYY'),
      to_date('28/02/2014','DD/MM/YYYY'),
      1
    FROM dual
    UNION
    SELECT 2,
      to_date('20/02/2014','DD/MM/YYYY'),
      to_date('20/03/2014','DD/MM/YYYY'),
      1
    FROM dual
    UNION
    SELECT 3,
      to_date('01/06/2014','DD/MM/YYYY'),
      to_date('30/06/2014','DD/MM/YYYY'),
      1
    FROM dual
  );
INSERT
INTO SITUATION
  (SELECT 12,
      to_date('01/02/2013','DD/MM/YYYY'),
      to_date('28/07/2013','DD/MM/YYYY'),
      2
    FROM dual
    UNION
    SELECT 22,
      to_date('20/11/2013','DD/MM/YYYY'),
      to_date('11/03/2014','DD/MM/YYYY'),
      2
    FROM dual
    UNION
    SELECT 32,
      to_date('17/06/2014','DD/MM/YYYY'),
      to_date('30/07/2014','DD/MM/YYYY'),
      2
    FROM dual
  );

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Table POSTE:

        ID NOM                                 DATEDEBUT DATEFIN 
---------- ----------------------------------- --------- ---------
         1 POSTE1                              01-JAN-14 31-DEC-14 
         2 POSTE2                              01-JAN-13 31-DEC-14 

Table Situation
      
        ID DATEDEBUTSIT DATEFINSIT    IDPOSTE
---------- ------------ ---------- ----------
         1 01-FEB-14    28-FEB-14           1 
         2 20-FEB-14    20-MAR-14           1 
         3 01-JUN-14    30-JUN-14           1 
        12 01-FEB-13    28-JUL-13           2 
        22 20-NOV-13    11-MAR-14           2 
        32 17-JUN-14    30-JUL-14           2 

 6 rows selected

Code:

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
 
WITH SITUATIONS AS
  (
  /* rajouter aux situations 2 lignes par poste
  ** ce qui permet faire debuter et terminer les situations vacantes respectivement
  ** au debut debut et fin de postes
  */
  SELECT id, DATEDEBUTSIT, DATEFINSIT,  idPoste FROM situation
  UNION
  SELECT 0 id, DATEDEBUT-1,DATEDEBUT-1,id idPoste FROM poste
  UNION
  SELECT 0 id,DATEFIN +1 DATEDEBUT, DATEFIN,id idPoste FROM poste
  ),
  VACATIONS AS
  (SELECT poste.id idPoste,
    NOM,
    poste.DATEDEBUT datedebPost,
    poste.DATEFIN dateFinPoste,
    situations.id,
    DATEDEBUTSIT,
    DATEFINSIT,
    /* le site est vacant le lendemain de la fin de situation*/
    DATEFINSIT +1 dateDebutVac,
    /* le site a pour date fin vacance la veille d'un debut situation*/
    LEAD (DATEDEBUTSIT, 1) OVER (Partition BY idPoste ORDER BY DATEDEBUTSIT)-1 dateFinVac,
    CASE
        /*il y a vacance seulement si dateDebutVac<= dateFinVac sinon 'NON VAC'
        ** NVL pour ne pas conciderer les vacances apres date fin de poste
        */
      WHEN DATEFINSIT+1>= NVL(LEAD(DATEDEBUTSIT, 1) OVER (Partition BY idPoste ORDER BYDATEDEBUTSIT),DATEFINSIT)
       THEN 'NON VAC'
       ELSE 'VAC'
    END vac
  FROM SITUATIONS,
    POSTE
  WHERE idPoste=poste.id
  )
SELECT nom, dateDebutVac,dateFinVac FROM VACATIONS WHERE vac='VAC';

RESULTATS :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
 
NOM                                 DATEDEBUTVAC DATEFINVAC
----------------------------------- ------------ ----------
POSTE1                              01-JAN-14    31-JAN-14  
POSTE1                              21-MAR-14    31-MAY-14  
POSTE1                              01-JUL-14    31-DEC-14  
POSTE2                              01-JAN-13    31-JAN-13  
POSTE2                              29-JUL-13    19-NOV-13  
POSTE2                              12-MAR-14    16-JUN-14  
POSTE2                              31-JUL-14    31-DEC-14  
 
 7 rows selected