Calcul avec des dates

Version imprimable

Bonjour,

Je dispose d'une table (SECA_ARE) dans laquelle dont enregistrée des évènements.
Elle contient les champs ARE_DEBUT et ARE_FIN de type DATETIME qui contiennent le début et la fin de l'évènement.

Je cherche à totaliser la durée de ces évènements sur une période bornée par @Debut et @Fin.
Pour celà j'utilise la procédure suivante.
Forcément mes bornes peuvent être à cheval entre le début et la fin de l'évènement.
C'est pour celà que j'effectue 4 requêtes consécutives.

Je ne vois pas d'autres solutions mais je me demande s'il n'y a pas plus simple.
Est-ce que celà est judicieux ?
Existe-t-il une autre méthode ?

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 30 31 32 DECLARE @Ligne AS INT,@Debut AS DATETIME,@Fin AS DATETIME SET @Ligne=1 SET @Debut='06/02/09 06:00' SET @Fin='06/02/09 14:00' -- Si ARE_DEBUT et ARE_FIN sont compris entre @Debut et @Fin SELECT datediff(minute,ARE_DEBUT,ARE_FIN)/60.0 DUREE FROM SECA_ARE WHERE LIGNE=@Ligne AND @Debut<=ARE_DEBUT AND ARE_FIN<=@Fin UNION -- Si ARE_DEBUT est compris entre @Debut et @Fin SELECT datediff(minute,ARE_DEBUT,@Fin)/60.0 DUREE FROM SECA_ARE WHERE LIGNE=@Ligne AND @Debut<=ARE_DEBUT AND @Fin>=ARE_DEBUT AND @Fin<ARE_FIN UNION -- Si ARE_FIN est compris entre @Debut et @Fin SELECT datediff(minute,@Debut,ARE_FIN)/60.0 DUREE FROM SECA_ARE WHERE LIGNE=@Ligne AND @Debut<=ARE_FIN AND @Fin>=ARE_FIN AND ARE_DEBUT<@Debut UNION -- Si @Debut et @Fin sont compris entre ARE_DEBUT et ARE_FIN SELECT datediff(minute,@Debut,@Fin)/60.0 DUREE FROM SECA_ARE WHERE LIGNE=@Ligne AND @Debut>ARE_DEBUT AND @Fin<ARE_FIN

07/02/2009, 22h35
Antoun

Quel est le SGBD ? SQL Server ?
09/02/2009, 08h48
SQLpro

Oui, on peut faire cela en une seule requête, mais c'est assez complexes.

A +

Salut !

Si tu arrives à construire les intervalles résultant de la "fusion" des intervalles imbriqués, sous la forme "date_debut, date_fin", tu peux simplement faire la somme des durées datediff(granularite, date_debut, datefin)

Tu as droit aux fonctions analytiques, j'espère ?
Disons oui... C'est parti !

La table :

Code:

1
2
3
4
5
 
CREATE TABLE SECA_ARE
( evt_id integer, 
are_debut date, 
are_fin date);

Les données de test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
INSERT INTO SECA_ARE VALUES (1, TO_DATE('20090101', 'yyyymmdd'), TO_DATE('20090110', 'yyyymmdd'));
INSERT INTO SECA_ARE VALUES (2, TO_DATE('20090102', 'yyyymmdd'), TO_DATE('20090104', 'yyyymmdd'));
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090108', 'yyyymmdd'), TO_DATE('20090115', 'yyyymmdd'));
 
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090118', 'yyyymmdd'), TO_DATE('20090120', 'yyyymmdd'));
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090119', 'yyyymmdd'), TO_DATE('20090125', 'yyyymmdd'));
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090120', 'yyyymmdd'), TO_DATE('20090122', 'yyyymmdd'));
 
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090201', 'yyyymmdd'), TO_DATE('20090210', 'yyyymmdd'));
 
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090215', 'yyyymmdd'), TO_DATE('20090222', 'yyyymmdd'));
INSERT INTO SECA_ARE VALUES (3, TO_DATE('20090216', 'yyyymmdd'), TO_DATE('20090224', 'yyyymmdd'));

Ordonne les évènements par date de début, attribue un numéro selon cet ordre, et pour chaque ligne récupère la date fin max jusqu'à cet ligne selon cet ordre :

Code:

1
2
3
 
select a.*, row_number() over(order by are_debut) rk, max(are_fin) over(order by are_debut)  dt_max
from seca_are a

Mettre en relation chaque ligne avec la ligne précédente,

Code:

1
2
3
4
5
6
7
8
 
WITH t AS (
select a.*, row_number() over(order by are_debut) rk, max(are_fin) over(order by are_debut)  dt_max
FROM seca_are a
)
select t.*, t1.*
from t left outer join t t1
  on t.rk = t1.rk + 1

Maintenant, on va détecter les "ruptures" d'intervalle. Ce sont les lignes telles que la date début est strictement supérieure à la date fin max des lignes précédentes.

Code:

1
2
3
4
5
6
7
8
 
WITH t AS (
select a.*, row_number() over(order by are_debut) rk, max(are_fin) over(order by are_debut)  dt_max
FROM seca_are a
)
select t.*, case when t.are_debut <= t1.dt_max  then 0 when t1.dt_max is null then 0 else 1 end as brk 
from t left outer join t t1
  on t.rk = t1.rk + 1*

On peut créer des groupes assez simplement en comptant à un stade donnée le nombre de breaks obtenus jusque là :

Code:

1
2
3
4
5
6
7
8
 
WITH t AS (
select a.*, row_number() over(order by are_debut) rk, max(are_fin) over(order by are_debut)  dt_max
FROM seca_are a
)
select t.*, sum(case when t.are_debut <= t1.dt_max  then 0 when t1.dt_max is null then 0 else 1 end) over(order by t.rk)
from t left outer join t t1
  on t.rk = t1.rk + 1

Et voilà, tu n'as plus qu'à déduire tes intervalles à partir de tes groupes, puis compter ce que tu veux...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH t AS (
select a.*, row_number() over(order by are_debut) rk, max(are_fin) over(order by are_debut)  dt_max
FROM seca_are a
)
select min(are_debut), max(dt_max)
from (
select t.*, sum(case when t.are_debut <= t1.dt_max  then 0 when t1.dt_max is null then 0 else 1 end) over(order by t.rk) grp
from t left outer join t t1
  on t.rk = t1.rk + 1
) res
group by grp

Résultat :

Code:

1
2
3
4
5
6
7
 
MIN(ARE_DEBUT)	MAX(DT_MAX)
 
18/01/09	25/01/09
01/02/09	10/02/09
15/02/09	24/02/09
01/01/09	15/01/09

(je te laisse tripoter le résultat pour avoir la durée totale...)
(Et j'espère ne pas avoir fait de raccourcis erronés dans mes raisonnements...)

11/02/2009, 13h41
marctoralba

Citation:

Envoyé par Antoun

Quel est le SGBD ? SQL Server ?

Il s'agit de SQL Server
11/02/2009, 13h42
marctoralba

Merci.

Je vais tester tout çà...