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
|
CREATE TEMPORARY TABLE r1
SELECT
CONCAT(MONTH(Heure_deb),'/',DAY(Heure_deb)) as 'Date',
Heure_deb,
Operateur.Id_op ,
Nom_op ,
Prenom_op,
Nom_act ,
TIME(Heure_deb) as heure,
Commentaire,
Type
FROM Operateur, Pointage, Activite
WHERE Operateur.Id_op = Pointage.Id_op
AND Activite.Id_act = Pointage.Id_act
ORDER BY date, Id_op, heure
;
Create temporary table r2
SELECT a.Id_op,
a.Heure_deb,
MIN(b.heure_deb) as fin, TIMEDIFF(b.Heure_deb, a.Heure_deb) as Time_Difference,
ROUND(HOUR(TIMEDIFF(b.Heure_deb, a.Heure_deb)) + MINUTE(TIMEDIFF(b.Heure_deb, a.Heure_deb))/60,2) as Decimal_duree
FROM Pointage a
LEFT JOIN Pointage b ON a.Id_op = b.Id_op
WHERE a.heure_deb < b.heure_deb
Group by a.Id_op, a.Heure_deb
;
Drop TABLE IF EXISTS Resultat;
CREATE TABLE Resultat
select
CONCAT(MONTH(r1.Heure_deb),'/',DAY(r1.Heure_deb)) as 'Date',
TIME(r1.Heure_deb) as heure,
r1.Id_op ,
Nom_op ,
Prenom_op,
Nom_act ,
Commentaire,
Type,
Time_Difference,
Decimal_duree,
r1.Heure_deb as Heure_deb
from r1
LEFT JOIN r2 ON r1.Id_op = r2.Id_op
and r1.heure_deb = r2.heure_deb
Order by Id_op, Heure_deb DESC |
Partager