Discussion :

[marctoralba]

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 :

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
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

[Antoun]

Quel est le SGBD ? SQL Server ?

[SQLpro]

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

A +

[pacmann]

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 :

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

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

Les données de test :

Code :

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

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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...)

[marctoralba]

Quel est le SGBD ? SQL Server ?

Il s'agit de SQL Server

[marctoralba]

Merci.

Je vais tester tout çà...