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
|
SELECT DISTINCT COUNT(I.ID_INTERVENTION_SDIS) AS Nbre_Inter,
AE.NOM_PRENOM_AGENT AS Nom_Agent,
AE.STATUT_POMPIER AS Statut,
CONVERT(VARCHAR,SUM(DATEDIFF(MI,AE.DH_ALERTE,AE.DH_FIN)))/60 AS Nbre_Heure,
C.LIB_CENTRE AS Centre,
CASE WHEN (DATEPART(HOUR,AE.DH_ALERTE)) BETWEEN 7 AND 18 THEN 'Jour' ELSE 'Nuit' END AS Journee,
CASE WHEN (T.FL_WEEKEND) = 1 THEN 'Week-end' ELSE '' END AS FL_WE,
YEAR(AE.DH_ALERTE) AS Annee
FROM OPS_INTERVENTION I
INNER JOIN OPS_AGENT_ENGAGE AE
ON AE.ID_INTERVENTION = I.ID_INTERVENTION
INNER JOIN OPS_CENTRE C
ON C.ID_CENTRE = AE.ID_CENTRE
INNER JOIN OPS_TEMPS T ON LEFT(T.DT,12) = LEFT(AE.DH_ALERTE,12)
WHERE
CONVERT(VARCHAR,(AE.DH_FIN-AE.DH_ALERTE),108) BETWEEN '00:05:00' AND '22:00:00'
AND T.FL_WEEKEND = 1
AND YEAR(AE.DH_ALERTE) = 2014
GROUP BY AE.NOM_PRENOM_AGENT ,
CASE WHEN (DATEPART(HOUR,AE.DH_ALERTE)) BETWEEN 7 AND 18 THEN 'Jour' ELSE 'Nuit' END,
AE.STATUT_POMPIER ,
C.LIB_CENTRE,
CASE WHEN (T.FL_WEEKEND) = 1 THEN 'Week-end' ELSE '' END,
YEAR(AE.DH_ALERTE) |
Partager