Décomposer une période par mois

**tnguyen05** · 08/05/2013, 23h02

Bonjour,

j'aimerais trouver un sql permettant de décomposer une période entre une date de début et une date de fin par mois;

exemple

id   date_deb     date_fin       qty par jour  
1    5/1/2013     13/3/2013          30  
2   29/01/2013    10/02/2013         10

=> deviendrait

id   date_deb     date_fin   nb_jour  qty_par_jour  qty periode  
1    1/1/2013    31/01/2013    27         30           810  
1    1/2/2013    28/02/2013    28         30           840  
1    1/3/2013    31/03/2013    13         30           390  
2    1/1/2013    31/01/2013     2         10            20  
2    1/2/2013    28/02/2013    10         10           100

merci de votre aide,

bien à vous,

**skuatamad** · 10/05/2013, 09h23

Voici une approche :

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
  WITH t AS (
SELECT 1 AS id, to_date('05/01/2013','dd/mm/yyyy') AS date_deb, to_date('13/03/2013','dd/mm/yyyy') AS date_fin, 30 AS qty_par_jour FROM dual union ALL
SELECT 2      , to_date('29/01/2013','dd/mm/yyyy')            , to_date('10/02/2013','dd/mm/yyyy')            , 10                 FROM dual union all
SELECT 3     , to_date('29/12/2012','dd/mm/yyyy')            , to_date('10/02/2013','dd/mm/yyyy')            , 10                 FROM dual union all
SELECT 4     , to_date('22/12/2012','dd/mm/yyyy')            , to_date('28/12/2012','dd/mm/yyyy')            , 10                 FROM dual
),       
       decomp_t AS (
SELECT t.*, x.column_value-1 AS lvl,
       add_months(trunc(date_deb,'MM'),x.column_value-1) AS new_date_deb,
       add_months(last_day(date_deb),x.column_value-1) AS new_date_fin
  FROM t
 CROSS JOIN TABLE(cast(multiset(
                           SELECT level
                             FROM dual
                          connect BY level <= months_between(last_day(date_fin), trunc(date_deb,'MM')) + 1 
                          ) AS sys.odcinumberlist) 
                   )x
)
SELECT id, new_date_deb, new_date_fin,
       least(date_fin, new_date_fin) - greatest(new_date_deb, date_deb) + 1  AS nb_jour,
       qty_par_jour, 
       qty_par_jour * (least(date_fin, new_date_fin) - greatest(new_date_deb, date_deb) + 1) AS qty_periode
  FROM decomp_t

Qui donne

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
 
        ID NEW_DATE NEW_DATE    NB_JOUR QTY_PAR_JOUR QTY_PERIODE
---------- -------- -------- ---------- ------------ -----------
         1 01/01/13 31/01/13         27           30         810
         1 01/02/13 28/02/13         28           30         840
         1 01/03/13 31/03/13         13           30         390
         2 01/01/13 31/01/13          3           10          30
         2 01/02/13 28/02/13         10           10         100
         3 01/12/12 31/12/12          3           10          30
         3 01/01/13 31/01/13         31           10         310
         3 01/02/13 28/02/13         10           10         100
         4 01/12/12 31/12/12          7           10          70
 
9 rows selected.
 
SQL>

**tnguyen05** · 10/05/2013, 12h19

salut skuatamad,

merci pour ta réponse,
je n'arrive pas à utilise ton sql
quand je lance il y a un message d'erreur pour 'add_months'
et 'multiset'

merci de prendre le temps de m'aider,
je suis sur SQL serveur.

bien à toi,

**skuatamad** · 10/05/2013, 12h38

Ici c'est le forum oracle.
Pour sqlserver, il faut aller dans le forum sqlserver

**tnguyen05** · 10/05/2013, 12h47

Salut,

oui je me suis rendu compte après

désolé, j'ai mis mon messager dans Sql server

est-ce que tu as une idée pour le faire en SQL server?
ou ta spécialité est Oracle,

bien à toi,

merci d'avoir pris le temps de me répondre

**skuatamad** · 10/05/2013, 16h41

Voici une version sqlserver

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
  WITH t AS (
SELECT 1 AS id, cast('2013-01-05' as date) AS date_deb, cast('2013-03-13' as date) AS date_fin, 30 AS qty_par_jour union ALL
SELECT 2      , cast('2013-01-29' as date)            , cast('2013-02-10' as date)            , 10                 union all
SELECT 3      , cast('2012-12-29' as date)            , cast('2013-02-10' as date)            , 10                 union all
SELECT 4      , cast('2012-12-22' as date)            , cast('2012-12-29' as date)            , 10                 
),
       gen_num as (
select 0 as num
 union all
select num + 1
  from gen_num
 where num + 1 < 12
), 
       decomp_t as (
select t.*,num, 
       DATEADD(month, DATEDIFF(month, num, dateadd(month,g.num,t.date_deb)), 0) AS new_date_deb,
       DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, dateadd(month,g.num,t.date_deb)) + 1, 0)) as new_date_fin
  from t
  join gen_num g on  g.num <= DATEDIFF(mm,t.date_deb,t.date_fin)
)
select id, new_date_deb, new_date_fin,
       DATEDIFF(day,case when new_date_deb > date_deb then new_date_deb else date_deb end,
                    case when new_date_fin < date_fin then new_date_fin else date_fin end) + 1 as nb_jour,
       qty_par_jour,
       (DATEDIFF(day,case when new_date_deb > date_deb then new_date_deb else date_deb end,
                     case when new_date_fin < date_fin then new_date_fin else date_fin end) + 1) * qty_par_jour as qty_periode
  from decomp_t   
 order by id, num

Et sinon la version oracle sur le même principe (au cas où quelqu'un soit intéressé car probablement plus performant que le multiset) :

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
SQL>   WITH t AS (
  2  SELECT 1 AS id, to_date('05/01/2013','dd/mm/yyyy') AS date_deb, to_date('13/03/2013','dd/mm/yyyy') AS date_fin, 30 AS qty_par_jour FROM dual union ALL
  3  SELECT 2      , to_date('29/01/2013','dd/mm/yyyy')            , to_date('10/02/2013','dd/mm/yyyy')            , 10                 FROM dual union all
  4  SELECT 3      , to_date('29/12/2012','dd/mm/yyyy')            , to_date('10/02/2013','dd/mm/yyyy')            , 10                 FROM dual union all
  5  SELECT 4      , to_date('22/12/2012','dd/mm/yyyy')            , to_date('28/12/2012','dd/mm/yyyy')            , 10                 FROM dual
  6  ),
  7         gen_num as (
  8  select level - 1 as num
  9    from dual
 10  connect by level < 12
 11  ),
 12         decomp_t AS (
 13  SELECT t.*, g.num AS lvl,
 14         add_months(trunc(date_deb,'MM'),g.num) AS new_date_deb,
 15         add_months(last_day(date_deb)  ,g.num) AS new_date_fin
 16    FROM t
 17    JOIN gen_num g on g.num <= months_between(last_day(date_fin), trunc(date_deb,'MM'))
 18  )
 19  SELECT id, new_date_deb, new_date_fin,
 20         least(date_fin, new_date_fin) - greatest(new_date_deb, date_deb) + 1  AS nb_jour,
 21         qty_par_jour,
 22         qty_par_jour * (least(date_fin, new_date_fin) - greatest(new_date_deb, date_deb) + 1) AS qty_periode
 23    FROM decomp_t
 24   order by id, lvl
 25  /
 
        ID NEW_DATE NEW_DATE    NB_JOUR QTY_PAR_JOUR QTY_PERIODE
---------- -------- -------- ---------- ------------ -----------
         1 01/01/13 31/01/13         27           30         810
         1 01/02/13 28/02/13         28           30         840
         1 01/03/13 31/03/13         13           30         390
         2 01/01/13 31/01/13          3           10          30
         2 01/02/13 28/02/13         10           10         100
         3 01/12/12 31/12/12          3           10          30
         3 01/01/13 31/01/13         31           10         310
         3 01/02/13 28/02/13         10           10         100
         4 01/12/12 31/12/12          7           10          70
 
9 rows selected.
 
SQL>

**tnguyen05** · 10/05/2013, 22h51

salut,

est-ce que je peux lancer la syntaxe en faisant un copie coller dans SQL server?
car j'ai des messages d'erreurs,

merci à toi,

**skuatamad** · 10/05/2013, 23h22

La version sqlserver proposée a été testée sur sqlserver 2008 donc un simple copié /collé devrait fonctionner.

A noter que la récursivité est, je pense, disponible à partir de la version 2005 (à confirmer par les experts sqlserver)
Cependant ici la récursivité n'est pas nécessaire il suffit de remplacer gen_num par 12 UNION ALL.

Par ailleurs T dans mon exemple sert à simuler ta table donc un simple copié/collé devrait fonctionner cependant afin de tester avec les vraies données le mot clé WITH doit rester mais directement suivi de gen_num (en remplaçant T par le nom de ta table, et le t.* par le nom des colonnes...)

Merci de préciser la version de sqlserver utilisée ainsi que le message d'erreur complet (code erreur + message)