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
| select numr_pers, dcd
from
(select tbase.numr_pers, dcd, nvl(tcorrection.correction_manu,0) as correction_manu, nvl(tdeja.deja_dcd,0) as deja_dcd
from
(select distinct numr_pers, first_value(valr_aprs_evnm) over (partition by numr_pers order by date_oprt desc ,heur_oprt desc) dcd
from daud_cre_admin
where code_evnm='DC02016'
and perd_arrt_info>=to_char((select date_debt_semn -7 from rdwh.periode_rest_bo),'YYYYMM')
and date_oprt>=(select date_debt_semn -7 from rdwh.periode_rest_bo)
and date_oprt<=(select date_fin_semn -7 from rdwh.periode_rest_bo)
and add_months(date_nais,216)<=date_oprt -- exclusion mineur
and valr_aprs_evnm is not null) tbase
left join
-- on exclut les clients pour lesquels il y a une correction
(select numr_pers, 1 as correction_manu
from
(select distinct numr_pers , first_value(valr_aprs_evnm) over(partition by numr_pers order by date_oprt desc, heur_oprt desc) as etat_dcd
from daud_cre_admin
where code_evnm='DC02016'
and perd_arrt_info<=to_char((select date_debt_semn from rdwh.periode_rest_bo),'YYYYMM')
and date_oprt<=(select date_fin_semn from rdwh.periode_rest_bo))
where etat_dcd is null) tcorrection on tbase.numr_pers = tcorrection .numr_pers
left join
-- on exclut les clients pour lesquels un deces a deja ete saisi 2 semaine avant
(select distinct numr_pers, 1 as deja_dcd
from daud_cre_admin
where code_evnm='DC02016'
and perd_arrt_info<=to_char((select date_debt_semn -7 from rdwh.periode_rest_bo),'YYYYMM')
and date_oprt<(select date_debt_semn -7 from rdwh.periode_rest_bo)
and valr_aprs_evnm is not null) tdeja on tbase.numr_pers = tdeja .numr_pers
)
where correction_manu = 0 -- exclusion détenteurs de credit immo
and deja_dcd =0)cible |
Partager