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
| select
PERSONN_MENS.MAT_EXT_NUM ,
if(PERSONN_MENS.ANC_CLI_IARD_PASS_DAT <> '0000-00-00' ,
PERSONN_MENS.ANC_CLI_IARD_PASS_DAT ,null),
if(PERSONN_QUOTI.SOUSC_IARD_PRE_DAT <> '0000-00-00',
year(CURRENT_DATE) - year(PERSONN_QUOTI.SOUSC_IARD_PRE_DAT),null),
PERSONN_MENS.PERS_GMPA ,
PERSONN_MENS.PERS_IARD_TYP ,
case
when PERSONN_MENS.PERS_IARD_TYP = 'C'
then 'Client IARD'
when PERSONN_MENS.PERS_IARD_TYP = 'A'
then 'Ancien client IARD'
when PERSONN_MENS.PERS_IARD_TYP = 'P'
then 'Pospect IARD'
else 'Autres'
end ,
PERSONN_MENS.PERS_NBR_CTR_IARD ,
case
when PERSONN_MENS.PERS_IARD_TYP = 'C' and PERSONN_MENS.PERS_VIE_TYP <> 'C'
then 'Mono IARD'
when PERSONN_MENS.PERS_IARD_TYP <> 'C' and PERSONN_MENS.PERS_VIE_TYP =
'C'
then 'Mono VIE'
when PERSONN_MENS.PERS_IARD_TYP = 'C' and PERSONN_MENS.PERS_VIE_TYP = 'C'
then 'IARD + VIE'
else 'Autres'
end ,
case
when PERSONN_QUOTI.PERS_BUR_RTC_IARD is null
then 'NUL'
else PERSONN_QUOTI.PERS_BUR_RTC_IARD
end ,
if(PERSONN_QUOTI.PERS_DECES_DAT<> '0000-00-00',PERSONN_QUOTI.PERS_DECES_DAT,
null),
if(PERSONN_QUOTI.PERS_NAISS_DAT<> '0000-00-00', PERSONN_QUOTI.PERS_DECES_DAT,
null),
year(CURRENT_DATE) - year(PERSONN_QUOTI.PERS_NAISS_DAT),
if(PERSONN_QUOTI.SOUSC_IARD_PRE_DAT<> '0000-00-00',PERSONN_QUOTI.PERS_DECES_DAT,
null)
from
PERSONN_MENS left join
PERSONN_QUOTI
on
(
PERSONN_MENS.MAT_EXT_NUM = PERSONN_QUOTI.MAT_EXT_NUM
) |
Partager