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 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
|
WITH CTE AS (I4.ID_INTERVENTION_SDIS AS 'N° Inter',
EE.DH_ALERTE AS 'DH Alerte',
EE4.DH_SLL AS 'DH SLL',
CONVERT(VARCHAR,(EE4.DH_SLL - EE4.DH_ALERTE),108) AS 'Délai Engin',
CASE WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 1 THEN '1er EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 2 THEN '2ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 3 THEN '3ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 4 THEN '4ème EP SLL'
END AS 'Ordre départ' ,
EE4.NUM_RENFORT,
EE4.NUM_ORDRE+''+EE4.NUM_RENFORT AS 'N° Départ',
CASE WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) <'00:15:00' THEN '< 15 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:15:00' AND '00:20:00' THEN '> 15 & < 20 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:20:00' AND '00:25:00' THEN '> 20 & < 25 mn'
ELSE '> 25 mn' END AS 'Interv. Délai',
CEN4.LIB_CENTRE AS 'Centre',
EE4.ID_TYPE_ENGIN AS 'Matériel',
CASE WHEN (EE4.ID_TYPE_ENGIN) IN ('CCFM','CCFM 3','FPT','FPTL','FPTR','CCFR','UCCFM','UCCFS') THEN 'Eng. Pompe'
WHEN (EE4.ID_TYPE_ENGIN) IN ('CCGC','CCGP','CCGC FDF') THEN 'Eng. Alim'
ELSE 'Moyen aérien' END AS 'Type engin',
EE4.FONCTION AS 'Fonct° Engin',
SIN4.LIB_SINISTRE AS 'Nom Sinistre',
SIN4.FAMILLE_SINISTRE AS 'Famille sinistre',
COM4.NOM_COMMUNE AS 'Commune',
I4.TYPE_LIEU+' '+I4.NOM_LIEU AS 'Adresse'
FROM OPS_INTERVENTION I4
INNER JOIN OPS_ENGIN_ENGAGE EE4 ON EE4.ID_INTERVENTION = I4.ID_INTERVENTION
INNER JOIN OPS_CENTRE CEN4 ON CEN4.ID_CENTRE = EE4.ID_CENTRE
INNER JOIN OPS_SINISTRE SIN4 ON SIN4.ID_SINISTRE = I4.ID_SINISTRE
INNER JOIN OPS_COMMUNE COM4 ON COM4.INSEE = I4.INSEE) AS 'Ordre départ',
SELECT DISTINCT I4.ID_INTERVENTION_SDIS AS 'N° Inter',
EE4.DH_ALERTE AS 'DH Alerte',
EE4.DH_SLL AS 'DH SLL',
CONVERT(VARCHAR,(EE4.DH_SLL - EE4.DH_ALERTE),108) AS 'Délai Engin',
CASE WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 1 THEN '1er EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 2 THEN '2ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 3 THEN '3ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 4 THEN '4ème EP SLL'
END AS 'Ordre départ' ,
EE4.NUM_RENFORT,
EE4.NUM_ORDRE+''+EE4.NUM_RENFORT AS 'N° Départ',
CASE WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) <'00:15:00' THEN '< 15 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:15:00' AND '00:20:00' THEN '> 15 & < 20 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:20:00' AND '00:25:00' THEN '> 20 & < 25 mn'
ELSE '> 25 mn' END AS 'Interv. Délai',
CEN4.LIB_CENTRE AS 'Centre',
EE4.ID_TYPE_ENGIN AS 'Matériel',
CASE WHEN (EE4.ID_TYPE_ENGIN) IN ('CCFM','CCFM 3','FPT','FPTL','FPTR','CCFR','UCCFM','UCCFS') THEN 'Eng. Pompe'
WHEN (EE4.ID_TYPE_ENGIN) IN ('CCGC','CCGP','CCGC FDF') THEN 'Eng. Alim'
ELSE 'Moyen aérien' END AS 'Type engin',
EE4.FONCTION AS 'Fonct° Engin',
SIN4.LIB_SINISTRE AS 'Nom Sinistre',
SIN4.FAMILLE_SINISTRE AS 'Famille sinistre',
COM4.NOM_COMMUNE AS 'Commune',
I4.TYPE_LIEU+' '+I4.NOM_LIEU AS 'Adresse'
FROM CTE
INNER JOIN OPS_ENGIN_ENGAGE EE4 ON EE4.ID_INTERVENTION = I4.ID_INTERVENTION
INNER JOIN OPS_CENTRE CEN4 ON CEN4.ID_CENTRE = EE4.ID_CENTRE
INNER JOIN OPS_SINISTRE SIN4 ON SIN4.ID_SINISTRE = I4.ID_SINISTRE
INNER JOIN OPS_COMMUNE COM4 ON COM4.INSEE = I4.INSEE
WHERE YEAR(EE4.DH_ALERTE) = 2013
AND SIN4.CATEGORIE_SINISTRE = 'INCENDIE'
AND COM4.INSEE IN ('47001','47157','47323')
AND EE4.NUM_ORDRE = 1
AND EE4.ID_TYPE_ENGIN IN ('CCFM','CCFM 3','FPT','FPTL','FPTR','CCFR','UCCFM','UCCFS')
AND EE4.DH_ALERTE = I4.DH_DIFFUSION_1ERE_ALERTE
GROUP BY I4.ID_INTERVENTION_SDIS,
EE4.DH_ALERTE,
EE4.DH_SLL,
EE4.NUM_RENFORT,
EE4.NUM_ORDRE+''+EE4.NUM_RENFORT,
EE4.NUM_ORDRE,
CEN4.LIB_CENTRE,
EE4.ID_TYPE_ENGIN,
EE4.FONCTION,
CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108),
CASE WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 1 THEN '1er EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 2 THEN '2ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 3 THEN '3ème EP SLL'
WHEN (ROW_NUMBER( ) OVER(PARTITION BY I4.ID_INTERVENTION_SDIS ORDER BY (EE4.DH_SLL-EE4.DH_ALERTE)))= 4 THEN '4ème EP SLL'
END,
CASE WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) <'00:15:00' THEN '< 15 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:15:00' AND '00:20:00' THEN '> 15 & < 20 mn'
WHEN CONVERT(VARCHAR,(EE4.DH_SLL-EE4.DH_ALERTE),108) BETWEEN '00:20:00' AND '00:25:00' THEN '> 20 & < 25 mn'
ELSE '> 25 mn' END,
SIN4.LIB_SINISTRE,
SIN4.FAMILLE_SINISTRE,
COM4.NOM_COMMUNE,
I4.TYPE_LIEU+' '+I4.NOM_LIEU,
I4.DH_DIFFUSION_1ERE_ALERTE,
I4.DH_SLL_1ER_ENGIN |
Partager