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
|
UPDATE P1
SET MPA_NB_AGE_BALANCE = P3.D
FROM SBI_DWH.dbo.TM_PARC P1
INNER JOIN
(
SELECT P.MPA_FK_JOUR, P.[MPA_FK_CLIENT] C,datediff(d,convert(datetime,cast(Max(P2.MPA_FK_JOUR AS varchar(8)),112),convert(datetime,cast(P.MPA_FK_JOUR AS varchar(8)),112)) ) D
FROM
SBI_DWh.dbo.TM_PARC P
INNER JOIN SBI_DWH.dbo.TM_PARC P2
ON P.MPA_FK_CLIENT = P2.MPA_FK_CLIENT
AND P2.MPA_FK_JOUR <= P.MPA_FK_JOUR
WHERE ISNULL([MPA_MT_BALANCE_AGEE],0) = 0 AND P.MPA_FK_JOUR>@max_dt
GROUP BY
P.[MPA_FK_JOUR]
,P.[MPA_FK_CLIENT]
,P.[MPA_FK_CLIENT_HISTO]
,P.[MPA_FK_BU]
,P.[MPA_FK_CONTRAT]
,P.[MPA_FK_CONTRAT_HISTO]
,P.[MPA_FK_ANCIENNETE]
,P.[MPA_FK_ENGAGEMENT_RESTANT]
,P.[MPA_FK_TERMINAL]
,P.[MPA_FK_OPERATEUR_DONNEUR]
,P.[MPA_FK_STATUT_PORTAGE_IN]
,P.[MPA_FK_OPERATEUR_RECEVEUR]
,P.[MPA_FK_STATUT_PORTAGE_OUT]
,P.[MPA_FK_CONTRAT_ACTIF]
,P.[MPA_FK_CONTRAT_ACTIF_ARCEP]
,P.[MPA_FK_OFFRE]
,P.[MPA_FK_CANAL_VENTE]
,P.MPA_FK_JOUR_RELANCE
,p.[MPA_MT_BALANCE_AGEE]
,P.[MPA_NB_PLAN_RELANCE]
) P3
ON P1.[MPA_FK_CLIENT]= P3.C
AND P1.MPA_FK_JOUR = P3.MPA_FK_JOUR
--MODIF POUR OPTIM
AND P1.MPA_FK_JOUR > @max_dt |
Partager