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
| select distinct STATS.T_DATA_SOURCE_REPORTING.LIBELLE_CAMPAGNE as campagne,
case MOAPP.SCF_SIGNALETIQ.DR_ID_COURT when '02' then 'BORDEAUX'
when '03' then 'PARIS'
when '07' then 'LILLE'
when '08' then 'LYON'
when '10' then 'MARSEILLE'
when '11' then 'NANCY'
when '16' then 'RENNES'
when '20' then 'IDF'
else null end as dr,
MOAPP.SCF_SIGNALETIQ.NOM_USUEL_PERE as groupe_plaque,
STATS.T_DATA_SOURCE_REPORTING.CD_RRF_COM as RRF,
MOAPP.SCF_SIGNALETIQ.NOM_COMMERCIAL,
STATS.T_DATA_SOURCE_REPORTING.DT_GENERATION date_commande,
upper (STATS.T_DATA_SOURCE_REPORTING.CANAL) as canal,
STATS.T_DATA_SOURCE_REPORTING.NUM_COMMANDE as numero_commande,
STATS.T_DATA_SOURCE_REPORTING.LIBELLE_COMMANDE,
count (*) as volume_commande
from STATS.T_DATA_SOURCE_REPORTING, MOAPP.SCF_SIGNALETIQ, MOAPP.CO001_CMMND
where STATS.T_DATA_SOURCE_REPORTING.CD_RRF_COM = MOAPP.SCF_SIGNALETIQ.AFFAIRE_ID
and STATS.T_DATA_SOURCE_REPORTING.NUM_COMMANDE = MOAPP.CO001_CMMND.CO001CMNUM
and STATS.T_DATA_SOURCE_REPORTING.ID_CAMPAGNE = 'CPG000191'
and STATS.T_DATA_SOURCE_REPORTING.NUM_COMMANDE not like 'centrl%'
and STATS.T_DATA_SOURCE_REPORTING.TYPE_DE_SORTIE in ('contacts', '@')
and MOAPP.CO001_CMMND.CO001ETAT in ('E', 'V')
group by STATS.T_DATA_SOURCE_REPORTING.LIBELLE_CAMPAGNE,
STATS.T_DATA_SOURCE_REPORTING.DT_GENERATION,
STATS.T_DATA_SOURCE_REPORTING.NUM_COMMANDE,
STATS.T_DATA_SOURCE_REPORTING.CANAL,
STATS.T_DATA_SOURCE_REPORTING.LIBELLE_COMMANDE,
STATS.T_DATA_SOURCE_REPORTING.CD_RRF_COM,
MOAPP.SCF_SIGNALETIQ.DR_ID_COURT,
MOAPP.SCF_SIGNALETIQ.NOM_COMMERCIAL,
MOAPP.SCF_SIGNALETIQ.NOM_USUEL_PERE
order by 2, 3, 4, 6 desc
; |
Partager