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
|
SELECT DISTINCT AE.ID_INTERVENTION Num_Inter,
CASE CHARINDEX(' ', REVERSE(AE.NOM_PRENOM_AGENT)) WHEN 2 THEN SUBSTRING(AE.NOM_PRENOM_AGENT, 1, LEN(AE.NOM_PRENOM_AGENT) - 2) ELSE AE.NOM_PRENOM_AGENT END Nom_Agent,
AE.ID_FONCTION Fonction,
AE.DH_ALERTE Alerte,
EE.DH_DEPART Depart,
EE.DH_SLL SLL,
EE.DH_DL Depart_Lieux,
EE.DH_RETOUR_DISPO Retour,
CAST((DATEDIFF(MI,EE.DH_DEPART,EE.DH_SLL)) AS char(3)) Nbre_Heure_Aller,
CAST((DATEDIFF(MI,EE.DH_DL,DH_RETOUR_DISPO)) AS char(3)) Nbre_Heure_Retour
FROM OPS_AGENT_ENGAGE AE
INNER JOIN OPS_ENGIN_ENGAGE EE ON EE.ID_INTERVENTION = AE.ID_INTERVENTION
AND EE.ID_ENGIN = AE.ID_ENGIN
AND EE.ID_CENTRE = AE.ID_CENTRE
WHERE AE.ID_FONCTION <> 'NR'
AND AE.ID_FONCTION LIKE 'COD %'
AND YEAR(EE.DH_ALERTE) = YEAR(GETDATE())
GROUP BY AE.ID_INTERVENTION,
CASE CHARINDEX(' ', REVERSE(AE.NOM_PRENOM_AGENT)) WHEN 2 THEN SUBSTRING(AE.NOM_PRENOM_AGENT, 1, LEN(AE.NOM_PRENOM_AGENT) - 2) ELSE AE.NOM_PRENOM_AGENT END,
AE.ID_FONCTION,
AE.DH_ALERTE,
EE.DH_DEPART,
EE.DH_SLL,
EE.DH_DL,
EE.DH_RETOUR_DISPO |
Partager