Requête sur les cumuls d'heures dans un créneau horaire

**Slayes** · 23/07/2013, 16h05

Bonjour à tous,

J'ai une table qui se présente de cette façon

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
id       heuredeb (int)        heurefin   (int)     idtechnicien
1        1353909600            1353931200            19
2        1353931200            1353963600           19
3        1353920400            1353938400            20
 
1353909600 = 26/11/2012 à 7:00:00 
1353931200 = 26/11/2012 à 13:00:00 
1353963600 = 26/11/2012 à 22:00:00 
1353920400 = 26/11/2012 à 10:00:00 
1353938400  = 26/11/2012 à 15:00:00

Je cherche à obtenir par idtechnicien, par jour le nom d'heure enregistré entre 08h00 et 17h00 et pour être honnête je patauge.

Si quelqu'un à une piste ...

**al1_24** · 23/07/2013, 16h26

Quelques questions indispensables pour pouvoir commencer à t'aider :

Qu'as-tu déjà essayé ?
Quel SGBD utilises-tu ?
Comment convertis-tu 1353909600 en "26/11/2012 à 7:00:00" ?

**Slayes** · 23/07/2013, 16h52

Oups ... je suis sous mysql

Pour les conversions, je les ai faites, grâce à http://www.timestamp.fr/?, c'était juste pour faciliter la lecture

Voici ce que j'ai essayé, ce n'est pas trés beau et il y a encore une erreur :
Edit : modif requete

Code :

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

1
2
3
4
5
SELECT 	IDTechnicien,Datedeb, Datefin, date(from_unixtime(Datedeb)),
	case WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%H-%d 08:00:00') ELSE from_unixtime(Datedeb,'%Y-%d-%m %H:%i:%s') END AS Deb,
	case WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%H-%d 16:00:00') ELSE from_unixtime(Datefin,'%Y-%d-%m %H:%i:%s') END AS Fin,        
        TIMEDIFF(case WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%m-%d 16:00:00') ELSE from_unixtime(Datefin,'%Y-%m-%d %H:%i:%s') END,case WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%m-%d 08:00:00') ELSE from_unixtime(Datedeb,'%Y-%m-%d %H:%i:%s') END) AS cumul
FROM `calendrier_technicien_abs`

**al1_24** · 23/07/2013, 17h27

Envoyé par Slayes

il y a encore une erreur

Laquelle ?

**Slayes** · 23/07/2013, 17h42

Voici la requête :

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
SELECT 	IDTechnicien,date(from_unixtime(Datedeb)) as Jour,Datedeb, Datefin, 
	case 
        WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%d-%m 08:00:00') ELSE from_unixtime(Datedeb,'%Y-%d-%m %H:%i:%s') 
    END AS Deb,
	case
	   WHEN date(from_unixtime(Datefin)) >date(from_unixtime(Datedeb)) then from_unixtime(Datedeb,'%Y-%d-%m 16:00:00')  
	   WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%d-%m 16:00:00') 
	   ELSE from_unixtime(Datefin,'%Y-%d-%m %H:%i:%s') 
    END AS Fin,        
    TIMEDIFF(case
	   WHEN date(from_unixtime(Datefin)) >date(from_unixtime(Datedeb)) then from_unixtime(Datedeb,'%Y-%d-%m 16:00:00')  
	   WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%d-%m 16:00:00') 
	   ELSE from_unixtime(Datefin,'%Y-%d-%m %H:%i:%s') 
    END,case 
        WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%d-%m 08:00:00') ELSE from_unixtime(Datedeb,'%Y-%d-%m %H:%i:%s') 
    END) AS cumul
FROM `calendrier_technicien_abs`
WHERE datefin > datedeb

Voici le résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
IDTechnicien 	Jour 	Datedeb 	Datefin 	Deb 	Fin 	cumul
19 	2012-11-26 	1353909600 	1354057140 	2012-26-11 08:00:00 	2012-26-11 16:00:00 	NULL
17 	2013-04-17 	1366174800 	1366200000 	2013-17-04 08:00:00 	2013-17-04 14:00:00 	NULL
16 	2012-11-01 	1351749600 	1351810740 	2012-01-11 08:00:00 	2012-01-11 16:00:00 	08:00:00
18 	2012-10-29 	1351512000 	1351551540 	2012-29-10 13:00:00 	2012-29-10 16:00:00 	NULL
16 	2012-11-21 	1353477600 	1353538740 	2012-21-11 08:00:00 	2012-21-11 16:00:00 	NULL
19 	2012-11-30 	1354262400 	1354275000 	2012-30-11 09:00:00 	2012-30-11 12:30:00 	NULL
17 	2012-12-28 	1356696000 	1356735540 	2012-28-12 13:00:00 	2012-28-12 16:00:00 	NULL
19 	2012-11-28 	1354107600 	1354143540 	2012-28-11 14:00:00 	2012-28-11 16:00:00 	NULL
18 	2013-05-01 	1367384400 	1367445540 	2013-01-05 08:00:00 	2013-01-05 16:00:00 	08:00:00

Edit :

Voici ca commence a prendre forme :

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
SELECT 	IDTechnicien,date(from_unixtime(Datedeb)) as Jour,Datedeb, Datefin, from_unixtime(Datedeb,'%Y-%m-%d %H:%i:%s') as deb2, from_unixtime(Datefin,'%Y-%m-%d %H:%i:%s') as fin2,
	case 
        WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%m-%d 08:00:00')
        WHEN hour(from_unixtime(Datedeb)) >16 then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')
		ELSE from_unixtime(Datedeb,'%Y-%m-%d %H:%i:%s') 
    END AS Deb,
	case
	   WHEN date(from_unixtime(Datefin)) >date(from_unixtime(Datedeb)) then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')  
	   WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%m-%d 16:00:00') 
	   WHEN hour(from_unixtime(Datefin)) <8 then from_unixtime(Datefin,'%Y-%m-%d 08:00:00') 
	   ELSE from_unixtime(Datefin,'%Y-%m-%d %H:%i:%s') 
    END AS Fin,        
    TIMEDIFF(case
	   WHEN date(from_unixtime(Datefin)) >date(from_unixtime(Datedeb)) then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')  
	   WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%m-%d 16:00:00') 
	   WHEN hour(from_unixtime(Datefin)) <8 then from_unixtime(Datefin,'%Y-%m-%d 08:00:00') 
	   ELSE from_unixtime(Datefin,'%Y-%m-%d %H:%i:%s') 
    END,case 
        WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%m-%d 08:00:00')
        WHEN hour(from_unixtime(Datedeb)) >16 then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')
		ELSE from_unixtime(Datedeb,'%Y-%m-%d %H:%i:%s') 
    END) AS cumul
FROM `calendrier_technicien_abs`
WHERE datefin > datedeb
GROUP BY IDTechnicien, Jour

Code :

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

1
2
3
4
5
6
IDTechnicien 	Jour 	Datedeb 	Datefin 	deb2 	fin2 	Deb 	Fin 	cumul
16 	2012-10-08 	1349694000 	1349733540 	2012-10-08 13:00:00 	2012-10-08 23:59:00 	2012-10-08 13:00:00 	2012-10-08 16:00:00 	03:00:00
16 	2012-10-09 	1349758800 	1349819940 	2012-10-09 07:00:00 	2012-10-09 23:59:00 	2012-10-09 08:00:00 	2012-10-09 16:00:00 	08:00:00
16 	2012-10-10 	1349845200 	1349906340 	2012-10-10 07:00:00 	2012-10-10 23:59:00 	2012-10-10 08:00:00 	2012-10-10 16:00:00 	08:00:00
16 	2012-10-11 	1349931600 	1349992740 	2012-10-11 07:00:00 	2012-10-11 23:59:00 	2012-10-11 08:00:00 	2012-10-11 16:00:00 	08:00:00
16 	2012-10-12 	1350018000 	1350079140 	2012-10-12 07:00:00 	2012-10-12 23:59:00 	2012-10-12 08:00:00 	2012-10-12 16:00:00 	08:00:00

Me reste à convertir le timediff en minute avant le group by

**Slayes** · 23/07/2013, 18h35

Voila, cela donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 	IDTechnicien,date(from_unixtime(Datedeb)) as Jour,       
    sum(round(TIME_TO_SEC(TIMEDIFF(case
	   WHEN date(from_unixtime(Datefin)) >date(from_unixtime(Datedeb)) then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')  
	   WHEN hour(from_unixtime(Datefin)) >16 then from_unixtime(Datefin,'%Y-%m-%d 16:00:00') 
	   WHEN hour(from_unixtime(Datefin)) <8 then from_unixtime(Datefin,'%Y-%m-%d 08:00:00') 
	   ELSE from_unixtime(Datefin,'%Y-%m-%d %H:%i:%s') 
    END,case 
        WHEN hour(from_unixtime(Datedeb)) <8 then from_unixtime(Datedeb,'%Y-%m-%d 08:00:00')
        WHEN hour(from_unixtime(Datedeb)) >16 then from_unixtime(Datedeb,'%Y-%m-%d 16:00:00')
		ELSE from_unixtime(Datedeb,'%Y-%m-%d %H:%i:%s') 
    END))/60) )AS cumul
FROM `calendrier_technicien_abs`
WHERE datefin > datedeb AND datedeb > UNIX_TIMESTAMP( current_timestamp( ) )
GROUP BY IDTechnicien, Jour

Si quelqu'un à une idée pour optimiser la requête car cela ne semble pas être top. Et de plus j'ai restreint aux plages si une seule journée

Requête sur les cumuls d'heures dans un créneau horaire

Langage SQL

Discussions similaires

Partager

Partager