PL/SQL mise a jour complexe

Version imprimable

04/10/2011, 15h40
pierre2008

PL/SQL mise a jour complexe

Bonjour,
J'ai la table suivante :

Citation:

ID |DEBUT |FIN |PERIODE|
1 |02/01/2010 |02/02/2010|MENSUEL|
1 |03/02/2010 |03/03/2010|MENSUEL|
1 |04/03/2010 |04/06/2010|TRIMESTRIEL|
1 |05/06/2010 |05/07/2010|MENSUEL|
1 |05/07/2010 |05/07/2011|ANNUEL|
1 |05/07/2011 |05/08/2011|MENSUEL|
1 |06/08/2011 |06/09/2011|MENSUEL|

et je veux les insérer dans une nouvelle table de la manière suivante :

Citation:

ID |DEBUT |FIN |PERIODE|
1 |02/01/2010 |03/03/2010|MENSUEL| --> regroupement ligne 1 et ligne 2
1 |04/03/2010 |04/06/2010|TRIMESTRIEL| --> ligne 3
1 |05/06/2010 |05/07/2010|MENSUEL| -->ligne 4
1 |05/07/2010 |05/07/2011|ANNUEL|-->ligne 5
1 |05/07/2011 |06/09/2011|MENSUEL|ligne 6 + 7

A noter que mes données sont ordonnées par date de début.
Les données sont regroupée par même période consécutive.

Je suis passé par du java pour le faire, je n'ai pas eu de soucis, j'aimerais le faire en pl/sql étant donné qu'il s'agit d'un script de migration.

Merci d'avance

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
With MaTable as
(
select 1 as id, date '2010-01-02' as debut, date '2010-02-02' as fin, 'MENSUEL' as periode from dual union all
select 1      , date '2010-02-03'         , date '2010-03-03'       , 'MENSUEL'            from dual union all
select 1      , date '2010-03-04'         , date '2010-06-04'       , 'TRIMESTRIEL'        from dual union all
select 1      , date '2010-06-05'         , date '2010-07-05'       , 'MENSUEL'            from dual union all
select 1      , date '2010-07-05'         , date '2011-07-05'       , 'ANNUEL'             from dual union all
select 1      , date '2011-07-05'         , date '2011-08-05'       , 'MENSUEL'            from dual union all
select 1      , date '2011-08-06'         , date '2011-09-06'       , 'MENSUEL'            from dual
)
  ,  SR as
(
select id, debut, fin, periode,
       row_number() over(partition by id          order by debut asc) -
       row_number() over(partition by id, periode order by debut asc) as grp
  from MaTable
)
  select id, min(debut) as debut, max(fin) as fin, periode
    from SR
group by id, periode, grp
order by id, debut;
 
        ID DEBUT      FIN        PERIODE    
---------- ---------- ---------- -----------
         1 02/01/2010 03/03/2010 MENSUEL    
         1 04/03/2010 04/06/2010 TRIMESTRIEL
         1 05/06/2010 05/07/2010 MENSUEL    
         1 05/07/2010 05/07/2011 ANNUEL     
         1 05/07/2011 06/09/2011 MENSUEL

Citation:

Envoyé par Waldar

En utilisant la méthode 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
With MaTable as
(
select 1 as id, date '2010-01-02' as debut, date '2010-02-02' as fin, 'MENSUEL' as periode from dual union all
select 1      , date '2010-02-03'         , date '2010-03-03'       , 'MENSUEL'            from dual union all
select 1      , date '2010-03-04'         , date '2010-06-04'       , 'TRIMESTRIEL'        from dual union all
select 1      , date '2010-06-05'         , date '2010-07-05'       , 'MENSUEL'            from dual union all
select 1      , date '2010-07-05'         , date '2011-07-05'       , 'ANNUEL'             from dual union all
select 1      , date '2011-07-05'         , date '2011-08-05'       , 'MENSUEL'            from dual union all
select 1      , date '2011-08-06'         , date '2011-09-06'       , 'MENSUEL'            from dual
)
  ,  SR as
(
select id, debut, fin, periode,
       row_number() over(partition by id          order by debut asc) -
       row_number() over(partition by id, periode order by debut asc) as grp
  from MaTable
)
  select id, min(debut) as debut, max(fin) as fin, periode
    from SR
group by id, periode, grp
order by id, debut;
 
        ID DEBUT      FIN        PERIODE    
---------- ---------- ---------- -----------
         1 02/01/2010 03/03/2010 MENSUEL    
         1 04/03/2010 04/06/2010 TRIMESTRIEL
         1 05/06/2010 05/07/2010 MENSUEL    
         1 05/07/2010 05/07/2011 ANNUEL     
         1 05/07/2011 06/09/2011 MENSUEL

Merci pour la solution, je l'ai adapté, elle marche très bien.
Encore merci.