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
| UPDATE t1
SET
t1.PGA_M_PRM_PER = t2.PRM_M_PRM_PURE
,t1.PGA_M_PRM_ANN = t2.PRM_M_PRM_PURE * 4
FROM FACT_PGA t1
INNER JOIN ( -- somme des primes pures
SELECT t2.PGA_D_RES, t2.PRM_POL_NO_POL, t2.PRM_GAR_KEY, t2.PRM_D_ECHEA, t2.PGA_D_DEB_VER, SUM(t1.PRM_M_PRM_PURE) PRM_M_PRM_PURE
FROM FACT_PRM t1
INNER JOIN ( -- recherche de date d'échéance max
SELECT t2.PGA_D_RES, t1.PRM_POL_NO_POL, t1.PRM_GAR_KEY, t2.PGA_D_DEB_VER, MAX(t1.PRM_D_ECHEA) PRM_D_ECHEA
FROM FACT_PRM t1
INNER JOIN DIM_GAR g1 ON g1.GAR_KEY = t1.PRM_GAR_KEY AND g1.GAR_TYP = 'COMPL'
INNER JOIN DIM_PRO p1 ON p1.PRO_KEY = t1.PRM_PRO_KEY AND p1.PRO_C_BRCH = '0012'
INNER JOIN FACT_PGA t2
ON t1.PRM_D_VAL <= t2.PGA_D_RES
AND t1.PRM_POL_NO_POL = t2.PGA_POL_NO_POL
AND t1.PRM_GAR_KEY = t2.PGA_GAR_KEY
WHERE t1.PRM_C_TYP_EMI = 'P' AND t1.PRM_SWI_ANNUL = 'N'
AND t2.PGA_D_RES IN (SELECT DISTINCT DAT FROM #d_res)
GROUP BY t2.PGA_D_RES, t1.PRM_POL_NO_POL, t1.PRM_GAR_KEY, t2.PGA_D_DEB_VER
) t2
ON t1.PRM_POL_NO_POL = t2.PRM_POL_NO_POL
AND t1.PRM_GAR_KEY = t2.PRM_GAR_KEY
AND t1.PRM_D_ECHEA = t2.PRM_D_ECHEA
WHERE t1.PRM_C_TYP_EMI = 'P' AND t1.PRM_SWI_ANNUL = 'N'
GROUP BY t2.PGA_D_RES, t2.PRM_POL_NO_POL, t2.PRM_GAR_KEY, t2.PRM_D_ECHEA, t2.PGA_D_DEB_VER
)t2
ON t1.PGA_D_RES = t2.PGA_D_RES
AND t1.PGA_POL_NO_POL = t2.PRM_POL_NO_POL
AND t1.PGA_GAR_KEY = t2.PRM_GAR_KEY
AND t1.PGA_D_DEB_VER = t2.PGA_D_DEB_VER
OPTION(FORCE ORDER) |
Partager