Récupérer temps entre croisement de dates

**azerto00** · 24/02/2012, 11h54

Bonjour à tous !

Je suis en train de bloquer sur ma requête.
Je fait des stattistiques sur un logiciel qui a un calendrier et qui stock des événements dans une table en gros sous la forme suivante :
________________________________
id--------DateDebut--------- DateFin

Mon but est d'obtenir le nombre d'heures effectuées.
Sachant que des dates peuvent se croiser et lorsque c'est le cas, il me faut compter seulement une seul fois le croisement.

Donc on pourrait avoir quelque chose comme ceci :
________________________________________
id-------DateDebut-------------------DateFin

X-------24 jan 2012 09h00-----------24 jan 2012 12h00
X-------25 jan 2012 12h00----------25 jan 2012 19h00
X-------25 jan 2012 16h30----------25 jan 2012 19h00
X-------26 jan 2012 16h00-----------26 jan 2012 18h00

Ce cas là, me donnerai :
24 jan: 3h00
25 jan: 4h30 + 2h30
26 jan: 2h00

Il y a-il quelque chose pour me sauver ?
A noter que les dates sont de type datetime (évidement)
Merci d'avance à tous.

**KookieMonster** · 24/02/2012, 12h27

Code :

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

1
2
3
4
5
6
7
8
9
 
with t_tmp(id, dateDeb, dateFin) as
(select 1, '20120124 09:00:00', '20120124 12:00:00' UNION
 select 2, '20120125 12:00:00', '20120125 19:00:00' UNION
 select 3, '20120125 16:30:00', '20120125 19:00:00' UNION
 select 4, '20120126 16:00:00', '20120126 18:00:00')
 select CAST(dateDeb as date) as JOUR,CONVERT(time(0), MAX(cast(dateFin as datetime))-MIN(cast(dateDEB as datetime))) as TPS_TOTAL
 from t_tmp
group by CAST(dateDeb as date)

renvoie bien
JOUR TPS_TOTAL
2012-01-24 03:00:00.0000000
2012-01-25 07:00:00.0000000
2012-01-26 02:00:00.0000000

Attention par contre si les événements s'étalent sur plusieurs jours. Dans cette requête, le nombre d'heures est comptabilisé sur la date de début.

**Ptit_Dje** · 24/02/2012, 14h41

Bonjour,

Je vous recommande la lecture de cet article.

A+

**azerto00** · 24/02/2012, 15h12

Re !

J'ai esseyé ton bout de code KookieMonster, mais probleme ..

Je tourne sur SqlServer 2005, donc pas de fonction time...
en enlevant time et donc :

Code :

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

1
2
3
4
5
6
7
8
WITH t_tmp(id, dateDeb, dateFin) AS
(SELECT 1, '20120124 09:00:00', '20120124 12:00:00' UNION
 SELECT 2, '20120125 12:00:00', '20120125 19:00:00' UNION
 SELECT 3, '20120125 16:30:00', '20120125 19:00:00' UNION
 SELECT 4, '20120126 16:00:00', '20120126 18:00:00')
 SELECT CAST(dateDeb AS datetime) AS JOUR, MAX(cast(dateFin AS datetime)-MIN(cast(dateDEB AS datetime))) AS TPS_TOTAL
 FROM t_tmp
GROUP BY CAST(dateDeb AS datetime)

J’obtiens l'erreur suivante :
Impossible d'exécuter une fonction d'agrégation sur une expression comportant un agrégat ou une sous-requête.
Je n'obtient donc pas le résultat adéquat.

**Waldar** · 24/02/2012, 16h43

Le MIN / MAX ne fonctionne pas non plus s'il y a des trous dans la journée, par exemple sur la ligne 2 :

Code :

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

SELECT 2, '20120125 12:00:00', '20120125 15:00:00'

**azerto00** · 24/02/2012, 17h03

Envoyé par Waldar

Le MIN / MAX ne fonctionne pas non plus s'il y a des trous dans la journée, par exemple sur la ligne 2 :

Code :

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

SELECT 2, '20120125 12:00:00', '20120125 15:00:00'

Je ne comprends pas l'histoire du trou dans cette journée

Concernant l'article , j'ai remarqué OVERLAPS mais je n'arrive pas à comprendre comment je pourrais l'utiliser pour me retourner une différence d'heure..

C'est la première fois que je suis devant un problème qui me parait bien complexe ... il y a bien une première fois à tout !

**Ptit_Dje** · 24/02/2012, 17h35

Bonjour,

En reference a l'article - 1.2.2 et la requete suivante:

Code :

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

1
2
3
SELECT *
FROM   PERIODE
WHERE  P1_DEBUT, P1_FIN OVERLAPS P2_DEBUT, P2_FIN

Partir sur une logique telle que:
Tant qu'il existe des periodes se chevauchant (recursion CTE)
{
- Generer des "nouvelles periodes" qui sont "la concatenation" des periodes se chevauchant.
}
Effectuer la somme des durees de toutes les periodes distinctes (celles distinctes directement et celles nouvellement redefinies)

**KookieMonster** · 24/02/2012, 17h59

Merci Waldar et Ptit Dje pour les utiles références à Overlaps, ainsi qu'au tutoriel de SqlPro (je n'avais pas lu celui-là).

**azerto00** · 01/03/2012, 09h33

Envoyé par Ptit_Dje

Partir sur une logique telle que:
Tant qu'il existe des periodes se chevauchant (recursion CTE)
{
- Generer des "nouvelles periodes" qui sont "la concatenation" des periodes se chevauchant.
}
Effectuer la somme des durées de toutes les périodes distinctes (celles distinctes directement et celles nouvellement redefinies)

Oui, cela ma parait logique ... mais jusqu'à présent, j'ai utilisé les bases de données (surtout MySQL) pour faire des requêtes assez simple (SELECT essentiellement). Je ne sais pas comment generer une variable pour ensuite faire une somme ....

J'ai regardé comment fonctionne la récursivité CTE mais comment dois-je m'y prendre ?
Un seule "fonction" récursive qui fait tout ? Ou une première requête qui cherche seulement les dates qui ne se chevauche pas puis fait le reste ?

Quelqu’un pourrait-il aider en me donnant un début d’implémentation ?

Merci d'avance

**azerto00** · 01/03/2012, 12h30

J'ai trouvé quelque chose qui pourrai me correspondre (lien)

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
 
SELECT `COUNT`, SEC_TO_TIME(SUM(Duration))
FROM (
    SELECT
        COUNT(*) AS `Count`,
        UNIX_TIMESTAMP(Times2.Time) - UNIX_TIMESTAMP(Times1.Time) AS Duration
    FROM (
        SELECT @rownum1 := @rownum1 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum1 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times1
    JOIN (
        SELECT @rownum2 := @rownum2 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum2 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times2
    ON Times1.rownum = Times2.rownum + 1
    JOIN events ON Times1.Time >= events.StartTime AND Times2.Time <= events.EndTime
    GROUP BY Times1.rownum
) Totals
GROUP BY `Count`

Cependant c'est pour du MySQL, et je souhaite l'adapter pour SQlServer .. Est-ce que cela marchera ?

**azerto00** · 02/03/2012, 10h08

J' ai trouvé une procédure qui fait ce que je souhaite, la voici :
Les éléments en gras seront à changer (voir les recevoir en paramètres)

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* This stored procedure combines any overlapping time segments into
a single segment in the temporary table #CombinedTime. Only non-
overlapping segements of time exist in this table. It then returns the
total hours for all these remaining segments.

Assumptions:
1. Date_Start <= Date_End
2. Date_Start, and Date_End are not null

Usage:
DECLARE @Total_Hours AS INT

EXEC @Total_Hours = CombinedTime

PRINT @Total_Hours
*/
ALTER PROCEDURE [dbo].[CombinedTime] 
AS

DECLARE
@Date_Start AS SMALLDATETIME,
@Date_End AS SMALLDATETIME,
@Total_Hours AS INT

/* Create temporary table to contain combined information.
*/
CREATE TABLE #CombinedTime (
Date_Start SMALLDATETIME NOT NULL ,
Date_End SMALLDATETIME NOT NULL
)


/* Use a cursor to get every record from Session_Temp */
DECLARE Time_Cursor CURSOR FOR
SELECT DateDebut, DateFin
FROM maTable WHERE DateDebut BETWEEN DateDebut AND DateFin)
ORDER BY DateDebut ASC

OPEN Time_Cursor
FETCH NEXT FROM Time_Cursor INTO @Date_Start, @Date_End
WHILE (@@FETCH_STATUS != -1)
BEGIN
/* Update an existing record in the temporary table if Date_Start is
within the bounds of an existing record, and the Date_End is outside */
UPDATE #CombinedTime
SET Date_End = @Date_End
WHERE @Date_Start BETWEEN Date_Start AND Date_End
AND @Date_End > Date_End

/* Insert a record that falls completely outside previous ranges */
INSERT INTO #CombinedTime
(Date_Start, Date_End)
SELECT @Date_Start, @Date_End
WHERE NOT EXISTS (SELECT 1
FROM #CombinedTime
WHERE @Date_Start < Date_End)


FETCH NEXT FROM Time_Cursor INTO @Date_Start, @Date_End
END /* WHILE */

CLOSE Time_Cursor
DEALLOCATE Time_Cursor

/* Get the total hours from the non-overlapping segments */
SELECT @Total_Hours = SUM(DATEDIFF(mi, Date_Start, Date_End))
FROM #CombinedTime

DROP TABLE #CombinedTime

**Ptit_Dje** · 02/03/2012, 14h26

Essayez ceci:

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
42
 
--Test data
WITH t_tmp(id, StartTime, EndTime) AS
(SELECT 1, '20120124 09:00:00', '20120124 12:00:00' UNION
 SELECT 2, '20120125 12:00:00', '20120125 19:00:00' UNION
 SELECT 3, '20120125 16:30:00', '20120125 20:00:00' UNION
 SELECT 5, '20120125 11:00:00', '20120125 12:00:00' UNION
 SELECT 7, '20120125 11:00:00', '20120125 11:30:00' UNION
 SELECT 8, '20120125 11:00:00', '20120125 11:30:00' UNION
 SELECT 3, '20120125 16:30:00', '20120125 20:00:00' UNION
 SELECT 4, '20120126 16:00:00', '20120126 18:00:00'
 )
,OrderedEnds AS (
SELECT cast(a.StartTime as DATE) PerDay, -- Par jour
       a.StartTime,
       a.EndTime,
       ROW_NUMBER() OVER(PARTITION BY cast(a.StartTime as DATE) ORDER BY cast(a.StartTime as DATE), a.EndTime desc) AS rn
FROM t_tmp a 
)
,StartAndEnds AS (
SELECT a.PerDay,
       a.StartTime,
       a.EndTime,
       a.rn
FROM OrderedEnds a
WHERE a.rn=1
 
UNION ALL
 
SELECT cast(b.StartTime as DATE) PerDay,
		case when b.StartTime < c.StartTime then b.StartTime
		else c.StartTime end as StartTime,
		case when b.EndTime < c.StartTime then b.EndTime
		else c.StartTime end as EndTime,
		b.rn
FROM OrderedEnds b
join StartAndEnds c
	on b.PerDay = c.PerDay
	and b.rn = c.rn + 1
)
select SUM(datediff(minute,starttime,EndTime)) from StartAndEnds
group by PerDay

C'est prevu pour des periodes qui tiennent sur 1 jour.
Si jamais votre periode s'etend sur 2 ou plusieurs jours, il faudra alors s'arranger pour splitter la periode en sous-periodes.
Solution inspiree de la solution du challenge tsql 49 de beyondrelational (pour les sources).