SELECT beneficiaire.idCollege,college.libelleCollege,COUNT(0) AS effectif
FROM  beneficiaire 
INNER JOIN college ON ((beneficiaire.idPolice=college.idPolice) AND (beneficiaire.idCollege=college.id))
WHERE beneficiaire.idPolice IN (184,193)
AND CASE WHEN sorti = "1" THEN dateSortie >= '2022-04-22' ELSE dateSortie IS NULL END
AND LEFT(dateEffet ,10)>='2022-01-01'  AND LEFT(dateEffet,10)<='2022-04-22' 
AND MONTH(LEFT(dateEffet,10))='1' 
AND  beneficiaire.idCollege=268

GROUP BY beneficiaire.idCollege,college.libelleCollege



SELECT DISTINCT idAvenant
FROM  beneficiaire 
INNER JOIN college ON ((beneficiaire.idPolice=college.idPolice) AND (beneficiaire.idCollege=college.id))
WHERE beneficiaire.idPolice IN (184,193)
AND CASE WHEN sorti = "1" THEN dateSortie >= '2022-04-22' ELSE dateSortie IS NULL END
AND LEFT(dateEffet ,10)>='2022-01-01'  AND LEFT(dateEffet,10)<='2022-04-22' 
AND MONTH(LEFT(dateEffet,10))='1' 
AND  beneficiaire.idCollege=268

GROUP BY beneficiaire.idCollege,college.libelleCollege


SELECT beneficiaire.*
FROM  beneficiaire 
INNER JOIN college ON ((beneficiaire.idPolice=college.idPolice) AND (beneficiaire.idCollege=college.id))
WHERE beneficiaire.idPolice IN (184,193)
AND idAvenant=309
/*AND CASE WHEN sorti = "1" THEN dateSortie >= '2022-04-22' ELSE dateSortie IS NULL END
AND LEFT(dateEffet ,10)>='2022-01-01'  AND LEFT(dateEffet,10)<='2022-04-22' 
AND MONTH(LEFT(dateEffet,10))='1' 
AND  beneficiaire.idCollege=268*/

GROUP BY beneficiaire.idCollege,college.libelleCollege