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
103
104
105
| SELECT
INC.INC_ID_CONTRAT as CON_IdContratKey
,CON.CON_ID_CONTRAT as REN_IdRenouvKey
,INC.INC_ID_ADHESION as CON_IdContrat
,CNT.CNT_CD_CENTRE as CEN_CodeCentre
,CON.CON_NUMERO as CON_NumContrat
,PRD.PRD_CD_PRODUIT as PRD_CodeProduit
,INC.INC_DT_SOUSCRIPTION as CON_DateAdhesion
,INC.INC_DT_REALISATION as CON_DateRealisation
,CON.CON_DT_DEBUT as CON_DateDebutContrat
,CON.CON_DT_FIN as CON_DateFinDeContrat
,CON.CON_DUREE_CONTRAT as CON_DureeContrat
,CASE WHEN SUBSTRING(CMB.CMB_RESULTAT, 1, 2) = 'OK'
THEN 'OK'
ELSE CASE WHEN CMB.CMB_ID_CONTRAT IS null
THEN 'NA'
ELSE 'KO'
END
END as CON_StatutAppelBienvenue
,CON.CON_TOP_TACITE as CON_TopTacite
,CONVERT(CHAR(6), INC.INC_DT_SOUSCRIPTION, 112) as CON_EcheanceCourante
,CON.CON_NB_LIGNE - 1 as ECH_NbrRenouvCourante
,CASE WHEN CON.CON_NB_LIGNE = 1
THEN '-' ELSE 'M' END
as CON_TypeEcheanceCourante
,ISNULL(CNR.CNR_CODE,'-') as TRE_TypeRenouvCourante
,CONVERT(CHAR(6), INC.INC_DT_REALISATION, 112) as CON_AnneeMoisDeRealisation
--,CASE WHEN CRB.CRB_ID_COORDONNEES_BANCAIRES IS NOT NULL AND RGP3.RGP_LIB ='Eligible AP-LIV'
/*,CASE WHEN INC0.INC_TOP_RIB in ('1','T') -- 23/09/2015
AND RGP3.RGP_LIB ='Eligible AP-LIV'
THEN '1' ELSE '0' END CON_TopDossierComplet*/
-- 11/04/2016 -- TFS7743 - Revoir flag DC dans ODS (Tacite ou Tacite à venir)
,CASE WHEN CON0.CON_TOP_TACITE in ('1','2','3','5') AND RGP3.RGP_LIB ='Eligible AP-LIV' THEN '1' ELSE '0' END CON_TopDossierComplet
,CASE RSV.RSV_LIB WHEN 'Vente à distance' THEN '1' ELSE '0' END CON_TopVD
,INC.INC_NUM_REF_GED as CON_NumStock
,RSV_LIB CON_ReseauVente
,CON0.CON_NUM_REF_GED
,CON0.CON_OPTION_VINCI
,CNC_CD_USER+CNC_TYPE_CONTACT Code_APPAFF
FROM dbo.T_CONTRAT_CON CON with (nolock)
INNER join T_CONTRAT_CON CON0 with (nolock)
ON CON.INC_ID_CONTRAT=CON0.INC_ID_CONTRAT
and CON0.CON_NB_LIGNE=1
INNER JOIN dbo.T_INFOS_CONTRAT_INC INC with (nolock)
ON CON.INC_ID_CONTRAT = INC.INC_ID_CONTRAT
AND INC.INC_ID_ADHESION IS NOT NULL
INNER JOIN dbo.T_INFOS_CONTRAT_INC INC0 with (nolock)
ON CON0.INC_ID_CONTRAT = INC0.INC_ID_CONTRAT
AND INC0.INC_ID_ADHESION IS NOT NULL
INNER JOIN T_PRODUIT_PRD PRD with (nolock)
ON CON.PRD_ID_PRODUIT = PRD.PRD_ID_PRODUIT
LEFT JOIN TJ_REGROUPEMENT_PRODUIT_RGP RGP3 with (nolock)
ON PRD.PRD_ID_PRODUIT = RGP3.PRD_ID_PRODUIT
AND RGP3.RGR_ID_REGROUPEMENT = (SELECT RGR_ID_REGROUPEMENT FROM TR_REGROUPEMENT_RGR WHERE RGR_ID_SOURCE='3')
LEFT JOIN dbo.TR_CANAL_RENOUVELLEMENT_CNR CNR with (nolock)
ON CON.CNR_ID_CANAL_RENOU = CNR.CNR_ID_CANAL_RENOU
LEFT OUTER JOIN (
SELECT distinct CMB.CMB_ID_CONTRAT, CMB.CMB_RESULTAT
from adhexp.T_CAMPAGNE_BIENVENUE_CMB CMB with (nolock)
join (
select distinct CMB_ID_CONTRAT, MAX(CMB_DT_APPEL) as CMB_DT_APPEL
from adhexp.T_CAMPAGNE_BIENVENUE_CMB with (nolock)
group by CMB_ID_CONTRAT
) MCM
on CMB.CMB_ID_CONTRAT = MCM.CMB_ID_CONTRAT
and CMB.CMB_DT_APPEL = MCM.CMB_DT_APPEL
) CMB
ON INC.INC_ID_ADHESION = CMB.CMB_ID_CONTRAT
INNER JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS CNICENTRE with (nolock)
ON CNICENTRE.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND CNICENTRE.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND CNICENTRE.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Distributeur')
INNER JOIN dbo.T_INDIVIDU_IND IND with (nolock)
ON CNICENTRE.IND_ID_INDIVIDU = IND.IND_ID_INDIVIDU
INNER JOIN dbo.T_CENTRE_CNT CNT with (nolock)
ON CNT.CNT_ID_CENTRE = IND.CNT_ID_CENTRE
INNER JOIN dbo.TR_RESEAU_VENTE_RSV RSV with (nolock)
ON INC.RSV_ID_RESEAU_VENTE = RSV.RSV_ID_RESEAU_VENTE
INNER JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS CNIADH with (nolock)
ON CNIADH.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND CNIADH.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND CNIADH.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Adhérent')
LEFT OUTER JOIN dbo.T_COORDONNEES_BANCAIRES_CRB CRB with (nolock)
ON CRB.IND_ID_INDIVIDU = CNIADH.IND_ID_INDIVIDU
AND CRB.CRB_TOP_PRINCIPAL = '1'
left JOIN dbo.TJ_CONTRAT_INDIVIDUS_CNI AS ConAPPAFF with (nolock)
ON ConAPPAFF.CON_ID_CONTRAT = CON.INC_ID_CONTRAT
AND ConAPPAFF.CNI_TOP_DERNIER_ACTEUR_ROLE = '1'
AND ConAPPAFF.RLI_ID_ROLE_INDIVIDU = (SELECT RLI_ID_ROLE_INDIVIDU FROM DBO.TR_ROLE_INDIVIDU_RLI WHERE RLI_LIB = 'Apporteur d''affaire')
left JOIN dbo.T_INDIVIDU_IND APPAFF with (nolock)
ON ConAPPAFF.IND_ID_INDIVIDU = APPAFF.IND_ID_INDIVIDU
left join T_CONTACT_CENTRE_CNC CNC
ON CNC.CNC_ID_CONTACT_CENTRE=APPAFF.CNC_ID_CONTACT_CENTRE
WHERE
(CON.CON_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CON.CON_DT_MODIFICATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CNICENTRE.CNI_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CRB_DT_CREATION_SYS >= DATEADD(DAY, -90, GETDATE())
OR CRB.CRB_DT_MODIFICATION_SYS >= DATEADD(DAY, -90, GETDATE())
)
--CON.CON_ID_CONTRAT='7FDE079C-6DC1-449B-9626-75486D290B30'
AND
CON.CON_TOP_DERNIERE_LIGNE = '1' |
Index Columstore question
, je dois surement mal m'y prendre...
Mais ici pour t'aider il nous faudra plus d'information sur le contexte. Est-ce que déjà tu peux nous donner la requête que tu exécutes? Le plan d'exécution réel ?
Répondre avec citation
Partager