Discussion :

[teen6517]

Bonjour tout le monde

Je cherche a faire une requete un peu trop compliqué pour moi

j'ai par exemple dans ma table deux colonnes (date debut et date fin)
je voudrais que si dans date début j'ai 15/06 et en date fin 25/08 mon select ne me ramene pas une ligne mais 3 avec :
date debut 15/06 date fin 30/06
date debut 01/07 date fin 31/07
date debut 01/08 date fin 25/08

mais je n'arrive pas a faire ce genre de découpage, j'espere que quelqu'un a des pistes
merci beaucoup

[Waldar]

Je ne suis pas complètement satisfait de la récupération du nombre de mois écoulé entre deux dates, mais ça fonctionne :

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
with MaTable as
(
select 1 as rid, date '2013-06-15' as dt_deb, date '2013-08-25' as dt_fin from dual union all
select 2       , date '2012-01-13'          , date '2012-01-27'           from dual union all
select 3       , date '2012-03-13'          , date '2012-04-10'           from dual union all
select 4       , date '2012-06-15'          , date '2012-07-15'           from dual
)
    select rid
         , case level
             when 1
             then dt_deb
             else trunc(add_months(dt_deb, level-1), 'mm')
           end as dt_deb
         , case level
             when floor(months_between(dt_fin, dt_deb)) + case when extract(day from dt_fin) < extract(day from dt_deb) then 2 else 1 end
             then dt_fin
             else last_day(add_months(dt_fin, level - floor(months_between(dt_fin, dt_deb)) - case when extract(day from dt_fin) < extract(day from dt_deb) then 2 else 1 end))
           end as dt_fin
      from MaTable
connect by level <= floor(months_between(dt_fin, dt_deb)) + case when extract(day from dt_fin) < extract(day from dt_deb) then 2 else 1 end
       and PRIOR rid = rid
       and PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  order by 1, 2;
 
RID DT_DEB     DT_FIN
--- ---------- ----------
  1 2013-06-15 2013-06-30
  1 2013-07-01 2013-07-31
  1 2013-08-01 2013-08-25
  2 2012-01-13 2012-01-27
  3 2012-03-13 2012-03-31
  3 2012-04-01 2012-04-10
  4 2012-06-15 2012-06-30
  4 2012-07-01 2012-07-15

[teen6517]

merci waldar

je vais tester pour voir si je peux l'addapter a mes tables

[pacmann]

Salut,

Rigolo ton connect by Waldar, je vais essayer de comprendre

Pour le nombre de mois, je ne sais pas si c'est mieux, mais il y a aussi ça :

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
 
SQL> WITH MaTable AS
  2  (
  3  SELECT 1 AS rid, date '2013-06-15' AS dt_deb, date '2013-08-25' AS dt_fin FROM dual union ALL
  4  SELECT 2       , date '2012-01-13'          , date '2012-01-27'           FROM dual union ALL
  5  SELECT 3       , date '2012-03-13'          , date '2012-04-10'           FROM dual union ALL
  6  SELECT 4       , date '2012-06-15'          , date '2012-07-15'           FROM dual
  7  )
  8  select to_char(dt_fin, 'YYYYMM') - to_char(dt_deb, 'YYYYMM') + 1 "mieux ?"
  9    , floor(months_between(dt_fin, dt_deb)) + case when extract(day FROM dt_fin) < extract(day FROM dt_deb) then 2 else 1 end "Celle qui ne te plait pas"
 10  from matable;
 
   mieux ? Celle qui ne te plait pas
---------- -------------------------
         3                         3
         1                         1
         2                         2
         2                         2

[Waldar]

On peut même virer les CASE en fait :

Code :

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

1
2
3
4
5
6
7
8
    select rid
         , greatest(dt_deb, trunc(add_months(dt_deb, level - 1), 'mm')) as dt_deb
         , least(dt_fin, last_day(add_months(dt_fin, level - floor(months_between(dt_fin, dt_deb)) - case when extract(day from dt_fin) < extract(day from dt_deb) then 2 else 1 end))) as dt_fin
      from MaTable
CONNECT BY level <= floor(months_between(dt_fin, dt_deb)) + case when extract(day from dt_fin) < extract(day from dt_deb) then 2 else 1 end
       and PRIOR rid = rid
       and PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  order by 1, 2;

Edit: @pacmann, les to_char ne fonctionnent pas très bien sur un changement d'année :

Code :

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

select 5       , date '2013-01-02'          , date '2014-02-01'           from dual

Edit2 : le CONNECT by prior dbms_random.value is not null c'est pour faire passer le CONNECT by directement sur les lignes d'une table unitairement.
C'est l'astuce de l'astuce !

[Waldar]

Par contre comme ça c'est nickel :

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
with MaTable as
(
select 1 as rid, date '2013-06-15' as dt_deb, date '2013-08-25' as dt_fin from dual union all
select 2       , date '2012-01-13'          , date '2012-01-27'           from dual union all
select 3       , date '2012-03-13'          , date '2012-04-10'           from dual union all
select 4       , date '2012-06-15'          , date '2012-07-15'           from dual union all
select 5       , date '2013-12-02'          , date '2014-02-01'           from dual
)
    SELECT rid
         , greatest(dt_deb, trunc(add_months(dt_deb, level - 1), 'mm')) AS dt_deb
         , least(dt_fin, last_day(add_months(dt_fin, level - 1 - months_between(trunc(dt_fin, 'mm'), trunc(dt_deb, 'mm'))))) AS dt_fin
      FROM MaTable
CONNECT BY level <= months_between(trunc(dt_fin, 'mm'), trunc(dt_deb, 'mm')) + 1
       AND PRIOR rid = rid
       AND PRIOR sys_guid() IS NOT NULL
  ORDER BY 1, 2;

[pacmann]

Edit: @pacmann, les to_char ne fonctionnent pas très bien sur un changement d'année :

Code :

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

select 5       , date '2013-01-02'          , date '2014-02-01'           from dual

Oui je suis con, j'aurais du multiplier les années par 12 où un truc du genre... mais bon ta trouvé la formule idéal, donc tout va bien

Edit2 : le CONNECT by prior dbms_random.value is not null c'est pour faire passer le CONNECT by directement sur les lignes d'une table unitairement.
C'est l'astuce de l'astuce !

Enorme, je crois que j'en ai toujours rêvé de ça !

[skuatamad]

Enorme, je crois que j'en ai toujours rêvé de ça !

Attention à cette astuce :
DBMS_RANDOM.value in hierarchical solution to string to table?

Dans le domaine des astuces sys_guid() est préférable.

[Waldar]

Alors va pour sys_guid(), j'édite la dernière requête.