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
journee as "Journée",
employe as "Employé",
sum((heure_sortie - heure_entree) * 24) AS "Temps de travail"
FROM
(
SELECT mt1.employe, mt1.journee, mt1.heure_entree, mt2.heure_sortie
FROM
(SELECT DISTINCT
heure AS heure_entree,
employe,
trunc(heure,'dd') AS journee,
rank() OVER (PARTITION BY employe ORDER BY heure) AS rang
FROM matable WHERE action = 'Entrée'
) mt1
INNER JOIN
(SELECT DISTINCT
heure AS heure_sortie,
employe,
trunc(heure,'dd') AS journee,
rank() OVER (PARTITION BY employe ORDER BY heure) AS rang
FROM matable WHERE action = 'Sortie'
) mt2
ON mt1.employe = mt2.employe
AND mt1.journee = mt2.journee
AND mt1.rang = mt2.rang
)
GROUP BY journee, employe
ORDER BY journee, employe |
Partager