SELECT DISTINCT C.id,C.libellePolice,A.idCollege,CL.libelleCollege,A.codeProduit,
		A.idAdherent,A.numeroAdherent,CONCAT(B.nom, " ", IFNULL(B.prenoms,"")) AS Adherent,
		A.id AS idBeneficiaire,A.numeroAdherent,CONCAT(A.nomBeneficiaire, " ", IFNULL(A.prenomsBeneficiaire,"")) AS Beneficiaire,
		P.libelle AS produit,A.numeroBeneficiaire,
		AV.codeTypeAvenant,A.codeLienParente,N.libelle AS naturepiece,
		A.numeroPiece,A.sexe, 
		DATE_FORMAT(A.dateNaissance, '%d/%m/%Y') dateNaissance,
		DATE_FORMAT(A.dateEffet, '%d/%m/%Y') dateEffet,
		DATE_FORMAT(A.dateEntree, '%d/%m/%Y') dateEntree,
		-- DATE_FORMAT(A.dateSortie, '%d/%m/%Y') dateSortie,
		CASE WHEN (A.dateSortie IS NOT NULL OR  A.dateSortie>'') THEN DATE_FORMAT(A.dateSortie, '%d/%m/%Y') ELSE DATE_FORMAT(E.dateEtat, '%d/%m/%Y') END AS dateSortie,
		CASE WHEN (M.libelle IS NOT NULL OR  M.libelle<>'') THEN M.libelle ELSE E.motifEtat END AS motifsortie
	FROM beneficiaire A 
	JOIN adherent B ON (B.id=A.idAdherent)
	JOIN police C ON (C.id=A.idPolice)	
	LEFT JOIN produit P ON (P.codeSociete=A.codeSociete AND P.codeProduit=A.codeProduit AND P.codeGcAssureur = C.codeGcAssureur)
	LEFT JOIN naturepiece N ON (N.codeSociete=A.codeSociete AND N.codeNaturePiece=A.codeNaturePiece)
	LEFT JOIN motifsortie M ON (M.codeMotifSortie=A.codeMotifSortie)
	LEFT JOIN etatpersonne H ON (H.codeEtatPersonne=A.codeEtatBeneficiaire)
	LEFT JOIN etatbeneficiaire E ON (E.idBeneficiaire=A.id)
	LEFT JOIN college CL ON (CL.id=A.idCollege AND CL.codeGcAssureur = C.codeGcAssureur)
	LEFT JOIN avenant AV ON (AV.id=A.idAvenant)
	WHERE (A.codeEtatBeneficiaire="SB") AND (E.codeEtatBeneficiaire="V")
	AND fn_id_entetecontrat_police(B.idPolice,CURDATE())=fn_id_entetecontrat_police_courant(B.idPolice)
	AND (A.idPolice=156)  AND (C.dateEffet<="2022-07-31") -- AND (A.dateEntree BETWEEN "2022-01-01" AND "2022-07-31") 
        ORDER BY C.id,A.idCollege,A.idAdherent,A.codeLienParente,A.id;