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
| SELECT
/*+ driving_site(infsal) */
VEHICULE.PARC,
T_AFFECT4.CLIENT,
T_AFFECT2.FILIALE,
sum(DONNEE_TX.DISTANCE),
DONNEE_TX.CONSO_TOT_AVG,
sum(DONNEE_TX.CONSO_TOTAL),
DONNEE_TX.DATEJ,
to_char(DONNEE_TX.DATEJ,'WW'),
case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end,
ETABSECT.LIB,
SOCIETE_PAND.LIB_SOC,
DONNEE_TX.ID_CONDUCTEUR,
DONNEE_TX.SPEED_AVG,
case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end,
sum(DONNEE_TX.CONSO_IDLE),
sum(DONNEE_TX.CONSO_COND),
MARQUE.LIBELLE,
CATVEHIC.LIBELLE,
decode(INDIVIDU.SEXE,'F','Femme','Homme'),
to_char(DONNEE_TX.DATEJ,'YYYYMM'),
round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2)
FROM
VEHICULE,
t_AFFECT4,
t_AFFECT2,
DONNEE_TX,
TR_TRA_PERSON,
INDIVIDU,
ETABSECT,
SOCIETE_PAND,
MARQUE,
CATVEHIC,
VEHICULE_AFFECTATION,
INFSAL
WHERE
( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID and VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ and VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ )
AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 )
AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 )
AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID )
AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE )
AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE )
AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE )
AND ( INFSAL.NOBUL(+)=1 and( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) and to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) )
AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR )
AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) )
AND ( INFSAL.SOCIETE=SOCIETE_PAND.SOCIETE(+) )
AND
(
(
SOCIETE_PAND.LIB_SOC = 'GT BRETAGNE'
OR
T_AFFECT2.FILIALE = 'BRETAGNE'
)
AND
DONNEE_TX.CONSO_TOT_AVG > 0
AND
DONNEE_TX.IND_CONF = '0'
AND
to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209'
)
GROUP BY
VEHICULE.PARC,
T_AFFECT4.CLIENT,
T_AFFECT2.FILIALE,
DONNEE_TX.CONSO_TOT_AVG,
DONNEE_TX.DATEJ,
to_char(DONNEE_TX.DATEJ,'WW'),
case INDIVIDU.NOM || 'toto' when 'toto' then TR_TRA_PERSON.NM_NAME else INDIVIDU.NOM || ' ' || INDIVIDU.PRENOM end,
ETABSECT.LIB,
SOCIETE_PAND.LIB_SOC,
DONNEE_TX.ID_CONDUCTEUR,
DONNEE_TX.SPEED_AVG,
case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30 à 39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 and MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40 à 49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end,
MARQUE.LIBELLE,
CATVEHIC.LIBELLE,
decode(INDIVIDU.SEXE,'F','Femme','Homme'),
to_char(DONNEE_TX.DATEJ,'YYYYMM'),
round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2)
HAVING
sum(DONNEE_TX.DISTANCE) > 0; |
Partager