	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l; 
	CREATE TEMPORARY TABLE temp_beneficiaire_l
	SELECT A.id AS idBeneficiaire, A.dateSortie
	FROM beneficiaire A 
	JOIN adherent B ON (B.id=A.idAdherent)
	JOIN police C ON (C.id=A.idPolice)	
	WHERE C.id IN (197,229,364,325,322,156,80,67,129,66,204,311,284,295,282,273,278,275,266,279,276,354,
	280,267,270,269,268,272,263,264,262,265,299,261,239,225,252,149,222,236,374,200,219,
	305,367,209,306,218,319,179,346,298,326,318,352,227,297,223,296,168,82,242,338,258,
	195,340,347,320,283,378,341,359,307,314,357,316,330,342,337,353,368,310,344,312,335,
	285,371,259,331,289,360,302,369,375,290,327,370,365,257,309,303,379,286,323,339,328,
	321,86,97,110,120,196,111,132,133,123,301,114,95,108,85,88,101,348,293,202,84,203,233,
	334,343,373,185,308,192,193,184,355,135,216,231,250,215,232,376,228,240,154,187,221,
	157,253,81,251,363,243,79,213,241,166,226,172,198,362,317,350,324,329,174,366,349,345,
	140,76,217,256,220,356,245,358,292,246,255,248,377,287,313,189,291,77,237,212,122,361,
	207,178,336,260,199,175,137,71,119,152,136,159,145,224,171,147,235,75,139,315,300,144,
	150,180,127); 
	
	DELETE FROM temp_beneficiaire_l  WHERE CASE WHEN YEAR(dateSortie)=YEAR('2023-04-25') THEN YEAR(dateSortie)<YEAR('2023-04-25')-1
						ELSE YEAR(dateSortie)<YEAR('2023-04-25') END ;
		
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l; 
	CREATE TEMPORARY TABLE temp_beneficiaire_l	
        SELECT 
		C.codeGcAssureur,C.libellePolice AS POLICE,C.id AS IDPOLICE,C.numeroPolice AS NUMPOLICE, 
		CASE WHEN P.codeBaseTarification='FAM' THEN IFNULL(P.primeFamille,0) ELSE IFNULL(P.primeIndividu,0) END AS PRIME,COUNT(idBeneficiaire)	EFFECTIFS
	FROM beneficiaire A 
	JOIN temp_beneficiaire_l L ON (L.idBeneficiaire=A.id)
	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 college CL ON (CL.id=A.idCollege AND CL.codeGcAssureur = C.codeGcAssureur)
	WHERE C.id IN (197,229,364,325,322,156,80,67,129,66,204,311,284,295,282,273,278,275,266,279,276,354,
	280,267,270,269,268,272,263,264,262,265,299,261,239,225,252,149,222,236,374,200,219,
	305,367,209,306,218,319,179,346,298,326,318,352,227,297,223,296,168,82,242,338,258,
	195,340,347,320,283,378,341,359,307,314,357,316,330,342,337,353,368,310,344,312,335,
	285,371,259,331,289,360,302,369,375,290,327,370,365,257,309,303,379,286,323,339,328,
	321,86,97,110,120,196,111,132,133,123,301,114,95,108,85,88,101,348,293,202,84,203,233,
	334,343,373,185,308,192,193,184,355,135,216,231,250,215,232,376,228,240,154,187,221,
	157,253,81,251,363,243,79,213,241,166,226,172,198,362,317,350,324,329,174,366,349,345,
	140,76,217,256,220,356,245,358,292,246,255,248,377,287,313,189,291,77,237,212,122,361,
	207,178,336,260,199,175,137,71,119,152,136,159,145,224,171,147,235,75,139,315,300,144,
	150,180,127)
	AND CASE WHEN A.sorti = "1" THEN A.dateSortie >= '2023-04-25' ELSE (A.dateSortie IS NULL OR A.dateSortie=(NULL))  END 
	AND (CASE WHEN '2023-04-25' IS NULL THEN A.dateEntree IS NOT NULL ELSE A.dateEntree <='2023-04-25' END)
	GROUP BY C.codeGcAssureur,C.libellePolice,C.id,C.numeroPolice,P.codeBaseTarification,P.primeIndividu,P.primeFamille 
	ORDER BY codeGcAssureur;		
	
	DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;	
