[Teradata] Calcul heures salariés

**LiPeR** · 18/05/2017, 18h08

Bonsoir,

Je suis actuellement à la recherche de la faisabilité d'un calcul d'heures en SQL sur teradata.

J'ai à disposition une table avec 3 colonnes :
Timestamp
Action
EmployeNr

J'aimerai savoir s'il est possible, en une requête, de calculer le solde horaire de chaque employé pour chaque jour.
Voici quelques exemples de lignes (déjà isolé à un seul employé) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
02.01.2017 06:39	Start
02.01.2017 06:41	Pause
02.01.2017 07:15	Start
02.01.2017 11:25	Pause
02.01.2017 12:07	Start
02.01.2017 16:03	Stop
03.01.2017 07:13	Start
03.01.2017 11:49	Pause
03.01.2017 12:32	Start
03.01.2017 16:27	Stop
04.01.2017 07:14	Start
04.01.2017 11:36	Pause
04.01.2017 12:19	Start
04.01.2017 16:15	Stop

Beaucoup de cas sont possibles :
- Pas ou plusieurs pause(s) durant la journée
- Une journée qui termine durant la nuit du lendemain (Stop à 02h du matin le lendemain)

La solution à mes yeux serait de traiter ceci via, par exemple, du code VB dans une base Access ou du PHP/MySQL. Cependant cela m'obligerai à exporter des données. Scénario que je souhaiterai éviter si possible.

Merci beaucoup d'avance pour vos réponses

Très bonne soirée.

Stéphane

**al1_24** · 19/05/2017, 09h53

La première chose à faire est de calculer les périodes travaillées : une période travaillée est identifiée par une heure de Start et l'heure de Pause ou Stop qui suit immédiatement.
Ensuite, effectuer le découpage des périodes qui chevauchent deux journées.
Tout cela peut s'effectuer dans une requête, en passant éventuellement par une ou plusieurs CTE (Expressions de table) pour rendre le tout lisible.

**al1_24** · 19/05/2017, 11h39

J'ai profité de quelques minutes de pause pour me pencher sur le problème.
J'ai détaillé toutes les étapes :

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
WITH    heure_debut --  Seulement les heures de début de période
    AS  (   SELECT  employenr   as  id_emp
                ,   "timestamp" as  heure   --  TIMESTAMP est un mot réservé du langage SQL, Teradata devrait le refuser
            FROM    matable
            WHERE   action  = 'start'
        )
    ,   heure_fin   --  Seulement les heures de fin de période
    AS  (   SELECT  employenr   as  id_emp
                ,   "timestamp" as  heure
            FROM    matable
            WHERE   action  IN ('stop', 'pause')
        )
    ,   periode_brute   --  Associer l'heure de début à l'heure de fin
    AS  (   SELECT  deb.id_emp
                ,   deb.heure       AS  heure_debut
                ,   MIN(fin.heure)  AS  heure_fin   --  La plus petite des heures de fin postérieures à l'heure de début
            FROM    heure_debut AS  deb
                INNER JOIN
                    heure_fin   AS  fin
                    ON  deb.id_emp  = fin.id_emp
                    AND deb.heure   < fin.heure
        )    
    ,   periode_detail  --  Découper les périodes qui chevauchent sur deux jours
    AS  (   SELECT  id_emp
                ,   heure_debut
                ,   heure_fin
            FROM    periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')  = TRUNC(heure_fin, 'ddd')    --  Début et fin le même jour
        UNION ALL
            SELECT  id_emp
                ,   heure_debut
                ,   TRUNC(heure_fin, 'ddd')             AS  heure_fin       --  Jusqu'à la fin de la journée
            FROM    periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')   < TRUNC(heure_fin, 'ddd')   --  Fin un autre jour
        UNION ALL
            SELECT  id_emp
                ,   TRUNC(heure_fin, 'ddd')             AS  heure_debut 
                ,   heure_fin
            FROM    periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')   < TRUNC(heure_fin, 'ddd')   --  Fin un autre jour
                AND TRUNC(heure_fin, 'ddd')     < heure_fin                 --  Cas particulier
        )
    ,   periode_duree   --  Extraire le jour et calculer la durée de chaque période
    AS  (   SELECT  id_emp
                ,   CAST(TRUNC(heure_debut, ddd) AS DATE)                       AS jour
                ,   CAST(heure_fin - heure_debut) AS INTERVAL HOUR TO SECOND)   AS duree
            FROM    periode_detail
        )
--  Regroupement final par jour et employé        
SELECT  id_emp
    ,   jour
    ,   SUM(duree)  AS duree
FROM    periode_duree
GROUP BY id_emp
    ,   jour
;

On peut bien sûr passer par beaucoup moins d'étapes.

Je n'ai pas de serveur Teradata sous la main pour tester la syntaxe mais tout le principe est là.

**LiPeR** · 19/05/2017, 22h13

Wow, je n'en demandais pas tant

Merci infiniment pour cette grande aide. Je vais regarder ça lundi et je vous tiens au courant.

**LiPeR** · 22/05/2017, 14h00

Merci encore pour votre précieuse aide al1_24 !

Si cela peut rendre service à d'autres personnes, voici la requête adaptée et fonctionnelle :

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
WITH
	v_periode_duree   --  Extraire le jour et calculer la durée de chaque période
    AS  (   SELECT  id_emp
                ,   CAST(TRUNC(heure_debut, 'ddd') AS DATE)                       AS jour
                ,   (heure_fin - heure_debut) DAY TO MINUTE   AS duree
            FROM    v_periode_detail
        )
 
	,  v_periode_detail  --  Découper les périodes qui chevauchent sur deux jours
    AS  (   SELECT  id_emp
                ,   heure_debut
                ,   heure_fin
            FROM    v_periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')  = TRUNC(heure_fin, 'ddd')    --  Début et fin le même jour
        UNION ALL
            SELECT  id_emp
                ,   heure_debut
                ,   TRUNC(heure_fin, 'ddd')             AS  heure_fin       --  Jusqu'à la fin de la journée
            FROM    v_periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')   < TRUNC(heure_fin, 'ddd')   --  Fin un autre jour
        UNION ALL
            SELECT  id_emp
                ,   TRUNC(heure_fin, 'ddd')             AS  heure_debut 
                ,   heure_fin
            FROM    v_periode_brute
            WHERE   TRUNC(heure_debut, 'ddd')   < TRUNC(heure_fin, 'ddd')   --  Fin un autre jour
                AND TRUNC(heure_fin, 'ddd')     < heure_fin                 --  Cas particulier
        )
	,  v_periode_brute   --  Associer l'heure de début à l'heure de fin
    AS  (   SELECT  deb.id_emp
                ,   deb.heure       AS  heure_debut
                ,   MIN(fin.heure)  AS  heure_fin   --  La plus petite des heures de fin postérieures à l'heure de début
            FROM    v_heure_debut AS  deb
                INNER JOIN
                    v_heure_fin   AS  fin
                    ON  deb.id_emp  = fin.id_emp
                    AND deb.heure   < fin.heure
            GROUP BY 1,2
        ) 
	,  v_heure_debut
	AS  (   SELECT  employenr   as  id_emp
                ,   c_timestamp as  heure   --  TIMESTAMP est un mot réservé du langage SQL, Teradata devrait le refuser
            FROM    TABLE
            WHERE   f_type  = 'start'
        )
    ,   v_heure_fin   --  Seulement les heures de fin de période
    AS  (   SELECT  employenr   as  id_emp
                ,   c_timestamp as  heure
            FROM    TABLE
            WHERE   f_type IN ('stop', 'pause')
        )
 
 
 
SELECT
	id_emp
    ,   jour AS f_day
    ,   SUM(duree) AS f_hours
FROM    v_periode_duree AS per
 
HAVING
	jour >= '2017-01-01'
GROUP BY 1,2,3
ORDER BY 2,3 DESC;

Durant l'adaptation, voici quelques remarques et questions.
- Les CTE doivent être définies à l'envers (cf http://community.teradata.com/t5/Dat...ht/true#M25735)
- Dans la requête proposée, la durée est avec la fonction CAST, pourquoi ?

Code :

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

1
2
3
4
SELECT  id_emp
                ,   CAST(TRUNC(heure_debut, ddd) AS DATE)                       AS jour
                ,   CAST(heure_fin - heure_debut) AS INTERVAL HOUR TO SECOND)   AS duree
            FROM    periode_detail

Ce même champ a du être interprété en tant que Jours avec précisions à la minute, dans certains cas, j'ai des valeurs de plus de 60 jours (surement des problèmes de syncro avec certains équipements...)

Code :

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

(heure_fin - heure_debut) DAY TO MINUTE   AS duree

- Question très bête, comment faire un filtre sur la somme finale de durée ? J'ai tenté plusieurs valeurs dans HAVING sans succès (INTERVAL '24' HOUR par ex)

Merci en tout cas beaucoup !!!

[Teradata] Calcul heures salariés

Autres SGBD

Discussions similaires

Partager

Partager