Discussion :

[flowertip]

Bonjour,

Je cherche à connaître la date de début et celle de fin en continuité entre plusieurs intervalles de dates.

Par exemple, si j'ai les périodes suivantes et triées dans cet ordre:
2009-02-08 2009-03-08
2010-05-01 2010-06-01
2009-03-09 2010-06-03
2008-01-01 2008-06-06
2010-06-02 2011-07-05

J'aimerais obtenir le résultat jusqu'au premier trou fonctionnel, soit 2009-02-08 2011-07-05.

Pouvez-vous m'aider?

Merci.

[Scriuiw]

A mon avis, vous ne nous dites pas tout.


Car en l'état, un MIN et un MAX suffiront pour obtenir la plus petite et la plus grande des dates mais je pense que votre demande est un peu plus complexe que cela.

[Michel.Priori]

Petit problème sympa

Voici un script réalisé sur SQL server (pas de MV dispo sous Oracle là tout de suite )


**** Edit : non ça ne marche pas
**** Nouvel edit : ça fonctionne
mais pas comme j'aurais voulu. A savoir : lister toutes les périodes continues, en fournissant, pour chaque ligne de période continue, la "date début période continue" et la "date de fin de période continue"

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
create table periode_continue
( D_Debut date
, D_Fin date)
go
 
insert into periode_continue
values
 ('20080101',	'20080606')
,('20090208',	'20090308')
,('20090309',	'20100603')
,('20100501',	'20100601')
,('20100602',	'20110705')
;
 
select * from periode_continue;
 
go
with 
 Ancre as 
 (select cast('20090208' as date) as debut)
, cte as
( select d_debut , d_fin
   from periode_continue
   where D_Debut in (select debut 
				   from ancre 
				 )
  union all
  select P.d_debut , P.d_fin
   from periode_continue p
	   inner join cte 
		  on p.d_debut between cte.D_Debut and dateadd(D,+2,cte.D_Fin)
		  and p.d_fin > cte.d_fin 
)
select min(d_debut) , max(D_Fin)  
  from cte

[Michel.Priori]

Selon les mêmes données que précédement :

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
with 
      periode_et_fin_prec as
	   (select p.* 
		 , Lag (D_fin,1,null) over (order by d_debut) as date_fin_precedante
	   from periode_continue p)
    , indicateur_nouvelle_periode as
	   (select date_fin_precedante, D_Debut, D_Fin
		  , case 
			 when date_fin_precedante is null then 1
			 when date_fin_precedante < dateadd(d,-1,D_Debut) then 1
			 else 0
		    end as Nouvelle_période
	   from periode_et_fin_prec)
    , numerotation_periode as
	   (select D_Debut, D_Fin, sum(Nouvelle_période) over (order by D_Debut) as sum
	   from indicateur_nouvelle_periode)
select min(d_debut) as date_debut 
    , max (d_fin) as date_fin 
from numerotation_periode
group by sum

[McM]

@Michel, évite les transformations de type, surtout sur les dates, après les débutants s'y perdent

Code :

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

1
2
create table periode_continue( D_Debut date, D_Fin date)
insert into periode_continue values ('20080101',	'20080606')

Je rejoins Scriuiw, même s'il n'a pas vu la ligne cachée de 2008. Je vais juste donner un code pour avoir la liste des plages continues.

Exemple pour avoir les plages
Table Plages = données
T2 : permet d'avoir toutes les dates entre le 01/01/2007 et le 31/12/2030
T3 : Permet d'avoir toutes les dates entre le 01/01/2007 et le 31/12/2030 qui existent dans une plage de données
Select final : Méthode tabibitosan (je crois que c'est comme ça que ça s'écrit) : permet d'avoir les plages continues

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
WITH table_plages AS (
SELECT TO_DATE('2008-01-01', 'RRRR-MM-DD') deb, TO_DATE('2008-06-06', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2009-02-08', 'RRRR-MM-DD') deb, TO_DATE('2009-03-08', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2009-03-09', 'RRRR-MM-DD') deb, TO_DATE('2010-06-03', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2010-05-01', 'RRRR-MM-DD') deb, TO_DATE('2010-06-01', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2010-06-02', 'RRRR-MM-DD') deb, TO_DATE('2011-07-05', 'RRRR-MM-DD') fin FROM DUAL
),
t2 AS (SELECT TO_DATE('01.01.2007', 'DD.MM.RRRR') + LEVEL -1 dte
FROM dual
CONNECT BY LEVEL < (TO_DATE('31.12.2030', 'DD.MM.RRRR') - TO_DATE('01.01.2007', 'DD.MM.RRRR') + 2)),
t3 AS (SELECT dte
FROM t2
WHERE EXISTS (SELECT 1 FROM table_plages t WHERE t2.dte BETWEEN t.deb AND t.fin ))
SELECT MIN(dte) AS deb, MAX(dte) AS fin
FROM (SELECT dte, dte - row_number() OVER(ORDER BY dte ASC) AS grp FROM t3 )
GROUP BY grp
ORDER BY MIN(dte)

DEB FIN
01/01/2008 06/06/2008
08/02/2009 05/07/2011

Bien sûr on peut limiter la liste des dates de T2 en fonction des données, mais je ne pense pas que le gain soit suffisant par rapport aux select supplémentaires.

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
WITH table_plages AS (
SELECT TO_DATE('2008-01-01', 'RRRR-MM-DD') deb, TO_DATE('2008-06-06', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2009-02-08', 'RRRR-MM-DD') deb, TO_DATE('2009-03-08', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2009-03-09', 'RRRR-MM-DD') deb, TO_DATE('2010-06-03', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2010-05-01', 'RRRR-MM-DD') deb, TO_DATE('2010-06-01', 'RRRR-MM-DD') fin FROM DUAL
UNION ALL SELECT TO_DATE('2010-06-02', 'RRRR-MM-DD') deb, TO_DATE('2011-07-05', 'RRRR-MM-DD') fin FROM DUAL
),
t2 AS (SELECT w.deb + LEVEL -1 dte
FROM dual, (SELECT MIN(deb) deb FROM table_plages) w 
connect BY LEVEL < (SELECT MAX(t.fin) - MIN(t.deb) +2 FROM table_plages t)),
t3 AS (SELECT dte
FROM t2
WHERE EXISTS (SELECT 1 FROM table_plages t WHERE t2.dte BETWEEN t.deb AND t.fin ))
SELECT MIN(dte) AS deb, MAX(dte) AS fin
FROM (SELECT dte, dte - row_number() OVER(ORDER BY dte ASC) AS grp FROM t3 )
GROUP BY grp
ORDER BY MIN(dte)

[Michel.Priori]

@McM : merci
je prend la méthode avec grand plaisir
+1

De plus si la demande est

Je cherche à connaître la date de début et celle de fin en continuité entre plusieurs intervalles de dates.

l'affichage avec cette méthode, à savoir 6/6/2008 <-> 8/2/2009, est plus élégante.

[McM]

Voici un exemple concret (et moins compliqué que les dates) que j'utilise dans un écrans de création de code article [NUMBER(8)] (sous Forms, la liste de valeur permet d'afficher les plages et la prochaine valeur utilisable).

Code :

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

1
2
3
4
SELECT MIN(article) AS deb, MAX(article) AS fin, CASE WHEN MAX(article) >= 99999999 THEN NULL ELSE MAX(article)+1 END AS a_util
FROM (SELECT article, article - row_number() OVER(ORDER BY article) AS grp FROM T_ARTICLE)
GROUP BY grp
ORDER BY MIN(article) desc

[JeitEmgie]

Pour les curieux, une solution avec MATCH_RECOGNIZE:
en supposant DATA(partition_key,from_dat, to_dat)

A. le merge

Code :

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

1
2
3
4
5
6
7
8
9
SELECT valid_from, valid_to FROM DATA
MATCH_RECOGNIZE (
    PARTITION BY partition_key
    ORDER BY from_dat, to_dat
    MEASURES match_number() MATCH, first(from_dat) AS valid_from, max(to_dat) AS valid_to
    PATTERN(merged* strt)
    DEFINE 
        merged AS max(to_dat) >= next(from_dat)
);

B. les trous

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
WITH merged_data AS (
    SELECT partition_key, valid_from, valid_to FROM DATA
    MATCH_RECOGNIZE (
        PARTITION BY partition_key
        ORDER BY from_dat, to_dat
        MEASURES first(from_dat) AS valid_from, max(to_dat) AS valid_to
        PATTERN(merged* strt)
        DEFINE 
            merged AS max(to_dat) >= next(from_dat)
    )
)
SELECT * FROM (
SELECT 
    valid_to + 1 AS hole_from,
    lead(valid_from, 1) over(ORDER BY valid_from) - 1 AS hole_to
FROM merged_data
)
WHERE hole_to IS NOT null
ORDER BY hole_from, hole_to
;