1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| CREATE OR REPLACE VIEW v_data_pointages AS
SELECT edt_id AS IDI,
CONCAT (UPPER(etu_nom),' ',etu_prenom) AS ETUDIANT,
edt_date AS LADATE,
WEEK(edt_date,1) AS SEMAINE,
DATE_FORMAT(edt_date, "%Y-%m") AS PERIODE,
' En attente des émargements' AS STATUT,
TIME(TIMEDIFF(edt_heure_fin,edt_heure_debut)) AS DUREE,
TIME_TO_SEC(TIME(TIMEDIFF(edt_heure_fin,edt_heure_debut))) AS DUREE_SECONDES
FROM edt
LEFT JOIN etudiants ON edt_grp_id IN (etu_grp1_id,etu_grp2_id,etu_grp3_id,etu_grp4_id,etu_grp5_id,etu_grp6_id,etu_grp7_id,etu_grp8_id,etu_grp9_id) AND etu_visible=1
WHERE edt_visible=1
AND edt_id NOT IN (SELECT DISTINCT point_edt_id FROM pointages WHERE point_etu_id=etu_id)
ORDER BY 2,3,4,5,6; |
Partager