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
| create table #LISTE_CLIENTS
(
CLI_ID numeric(16),
CLI_ORIGINE char(1),
ANNEE int,
MOIS int,
PGE_Q_LBL_PRODUIT varchar(32),
PSU_CODE char(4),
FTP_CODE char(4),
FAC_CA money,
FAC_VOL numeric(30,2)
)
create index PK_MKT_LISTE_CLIENTS_id1 on #LISTE_CLIENTS (CLI_ID)
create index PK_MKT_LISTE_CLIENTS_id2 on #LISTE_CLIENTS (CLI_ORIGINE)
create index PK_MKT_EFFEC_PREC_id3 on #EFFEC_PREC (FTP_CODE)
create index PK_MKT_EFFEC_PREC_id4 on #EFFEC_PREC (PSU_CODE)
Insert into #LISTE_CLIENTS
select
distinct
T1.CLI_ID,
T1.CLI_ORIGINE,
T2.FAC_ANNEE,
T2.FAC_MOIS,
case when T3.PGE_Q_LBL_PRODUIT is NULL or T3.PGE_Q_LBL_PRODUIT='' then "NON IDENTIFIE" else T3.PGE_Q_LBL_PRODUIT end,
case when T3.PSU_CODE is NULL or T3.PSU_CODE='' then "N.I" else T3.PSU_CODE end,
case when T3.FTP_CODE is NULL or T3.FTP_CODE='' then "N.I" else T3.FTP_CODE end,
sum(T2.FAC_CA),
sum(T2.FAC_VOL)
from
dm_Horus_exp.dbo.MS_CLIENT T1,
dm_Horus_exp.dbo.MS_FACTURE T2,
dm_Horus_exp.dbo.MS_PRESTATION T3,
dm_Horus_exp.dbo.MS_FAC_PRESTA T4
where
T3.PSU_CODE in ('THEM','GEO','CFCA','CCBK','CTIN','CSIM','CNET','CSOF','COPT','CCSC','AFFI','CPL','CPL2','FGPP') or T3.FTP_CODE in ('ADR','MAIL','GEO')
and T1.CLI_ID=T2.CLI_ID
and T2.FAC_NO=T4.FAC_NO
and T4.PGE_ID=T3.PGE_ID
and datepart(yy, T2.FAC_DATE)=2006
--and datepart(yy, T2.CTR_DATE_DISTRI_FIN)=@AnneeRef
group by T1.CLI_ID, T1.CLI_ORIGINE, T2.FAC_ANNEE, T2.FAC_MOIS, T3.FTP_CODE, T3.PGE_Q_LBL_PRODUIT, T3.PSU_CODE |
Partager