1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
SELECT DISTINCT F_CRT_CONTRAT.NM_CODI_CONTRAT, PERSONNE.NOM, PERSONNE.PRENOM, ADRESSE, CODE_POSTAL, VILLE, IBAN, ADRESSE_BANCAIRE, CH_TITULAIRE
FROM F_CRT_CONTRAT, F_CRT_INTERVENANT, PERSONNE, F_CRT_ASSURE_BENEFICIAIRE
LEFT OUTER JOIN ADR_POST ON (F_CRT_ASSURE_BENEFICIAIRE.NM_IDENTIF_ASSURE = ADR_POST.NM_IDENTIF_PERSONNE)
LEFT OUTER JOIN ADR_BAN ON (F_CRT_ASSURE_BENEFICIAIRE.NM_IDENTIF_ASSURE = ADR_BAN.NM_IDENTIF_PERSONNE)
WHERE F_CRT_CONTRAT.NM_CODI_CONTRAT = F_CRT_INTERVENANT.NM_CODI_CONTRAT
AND F_CRT_INTERVENANT.NM_IDENTIF = F_CRT_ASSURE_BENEFICIAIRE.NM_IDENTIF_ASSURE
AND F_CRT_ASSURE_BENEFICIAIRE.NM_IDENTIF_BENEFICIAIRE = PERSONNE.NM_IDENTIF_PERSONNE
AND NM_STATUT_CRT = 1
AND (NM_CODI_PRODUIT = 4 OR NM_CODI_PRODUIT = 5)
AND ('14/04/2014'
BETWEEN F_CRT_INTERVENANT.DT_EFFET_TECH AND COALESCE(F_CRT_INTERVENANT.DT_FIN_TECH, CAST('9999-12-31' AS DATE))
OR '14/04/2014'
BETWEEN F_CRT_CONTRAT.DT_EFFET_CRT AND COALESCE(F_CRT_CONTRAT.DT_FIN_CRT, CAST('9999-12-31' AS DATE)))
AND PERSONNE.NM_IDENTIF_PERSONNE = '1170826'
; |
Partager