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