SELECT * FROM (

SELECT 0 AS ORDRE,'FRAIS REEL "CS"' AS LIBELLE,codePrestataire AS CODEP,fn_libelle_prestataire(codePrestataire) AS PRESTATAIRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='1' THEN SUM(JANVIER) ELSE '0' END AS JANVIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='2' THEN SUM(FEVRIER) ELSE '0' END AS FEVRIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='3' THEN SUM(MARS) ELSE '0' END AS MARS,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='4' THEN SUM(AVRIL) ELSE '0' END AS AVRIL,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='5' THEN SUM(MAI) ELSE '0' END AS MAI,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='6' THEN SUM(JUIN) ELSE '0' END AS JUIN,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='7' THEN SUM(JUILLET) ELSE '0' END AS JUILLET,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='8' THEN SUM(AOUT) ELSE '0' END AS AOUT,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='9' THEN SUM(SEPTEMBRE) ELSE '0' END AS SEPTEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='10' THEN SUM(OCTOBRE) ELSE '0' END AS OCTOBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='11' THEN SUM(NOVEMBRE) ELSE '0' END AS NOVEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='12' THEN SUM(DECEMBRE) ELSE '0' END AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) +SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN SUM(prt.fraisReel) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN SUM(prt.fraisReel) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN SUM(prt.fraisReel) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN SUM(prt.fraisReel) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN SUM(prt.fraisReel) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN SUM(prt.fraisReel) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN SUM(prt.fraisReel) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN SUM(prt.fraisReel) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN SUM(prt.fraisReel) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN SUM(prt.fraisReel) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN SUM(prt.fraisReel) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN SUM(prt.fraisReel) ELSE '0' END 'DECEMBRE'
		FROM p_prestationactes prt 
		INNER JOIN p_facture B ON (B.id=prt.idfacture)
		INNER JOIN police C ON (B.idPolice = C.id)
		INNER JOIN gc_assureur G ON (C.codeGcAssureur = G.codeGcAssureur)
		INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND (a.id=B.idAdherent))
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
		AND (CASE WHEN 'FIDRA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='FIDRA' END)  
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete='vitalis') AND prt.codeActe='CS'
		AND prt.ententePrealable NOT IN ('2','9') AND B.okDecompte=1 AND B.valide=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,B.codePrestataire,C.libellePolice		 
	) depense GROUP BY codePrestataire
UNION ALL
SELECT 1 AS ORDRE,'MONTANT REMB "CS"' AS LIBELLE,codePrestataire AS CODEP,fn_libelle_prestataire(codePrestataire) AS PRESTATAIRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='1' THEN SUM(JANVIER) ELSE '0' END AS JANVIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='2' THEN SUM(FEVRIER) ELSE '0' END AS FEVRIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='3' THEN SUM(MARS) ELSE '0' END AS MARS,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='4' THEN SUM(AVRIL) ELSE '0' END AS AVRIL,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='5' THEN SUM(MAI) ELSE '0' END AS MAI,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='6' THEN SUM(JUIN) ELSE '0' END AS JUIN,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='7' THEN SUM(JUILLET) ELSE '0' END AS JUILLET,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='8' THEN SUM(AOUT) ELSE '0' END AS AOUT,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='9' THEN SUM(SEPTEMBRE) ELSE '0' END AS SEPTEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='10' THEN SUM(OCTOBRE) ELSE '0' END AS OCTOBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='11' THEN SUM(NOVEMBRE) ELSE '0' END AS NOVEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='12' THEN SUM(DECEMBRE) ELSE '0' END AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) +SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN SUM(prt.montantApayer) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN SUM(prt.montantApayer) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN SUM(prt.montantApayer) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN SUM(prt.montantApayer) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN SUM(prt.montantApayer) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN SUM(prt.montantApayer) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN SUM(prt.montantApayer) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN SUM(prt.montantApayer) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN SUM(prt.montantApayer) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN SUM(prt.montantApayer) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN SUM(prt.montantApayer) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN SUM(prt.montantApayer) ELSE '0' END 'DECEMBRE'
		FROM p_prestationactes prt 
		INNER JOIN p_facture B ON (B.id=prt.idfacture)
		INNER JOIN police C ON (B.idPolice = C.id)
		INNER JOIN gc_assureur G ON (C.codeGcAssureur = G.codeGcAssureur)
		INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND (a.id=B.idAdherent))
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
		AND (CASE WHEN 'FIDRA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='FIDRA' END)  
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete='vitalis') AND prt.codeActe='CS'
		AND prt.ententePrealable NOT IN ('2','9') AND B.okDecompte=1 AND B.valide=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,B.codePrestataire,C.libellePolice		 
	) depense GROUP BY codePrestataire
	
UNION ALL 	

SELECT 2 AS ORDRE,'DECOMPTE "CS"' AS LIBELLE,codePrestataire AS CODEP,fn_libelle_prestataire(codePrestataire) AS PRESTATAIRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='1' THEN SUM(JANVIER) ELSE '0' END AS JANVIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='2' THEN SUM(FEVRIER) ELSE '0' END AS FEVRIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='3' THEN SUM(MARS) ELSE '0' END AS MARS,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='4' THEN SUM(AVRIL) ELSE '0' END AS AVRIL,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='5' THEN SUM(MAI) ELSE '0' END AS MAI,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='6' THEN SUM(JUIN) ELSE '0' END AS JUIN,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='7' THEN SUM(JUILLET) ELSE '0' END AS JUILLET,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='8' THEN SUM(AOUT) ELSE '0' END AS AOUT,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='9' THEN SUM(SEPTEMBRE) ELSE '0' END AS SEPTEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='10' THEN SUM(OCTOBRE) ELSE '0' END AS OCTOBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='11' THEN SUM(NOVEMBRE) ELSE '0' END AS NOVEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='12' THEN SUM(DECEMBRE) ELSE '0' END AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) +SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COUNT(*)ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COUNT(*)ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COUNT(*)ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COUNT(*)ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COUNT(*)ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COUNT(*)ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COUNT(*)ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COUNT(*)ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COUNT(*)ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COUNT(*)ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COUNT(*)ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COUNT(*)ELSE '0' END 'DECEMBRE'
		FROM p_prestationactes prt 
		INNER JOIN p_facture B ON (B.id=prt.idfacture)
		INNER JOIN police C ON (B.idPolice = C.id)
		INNER JOIN gc_assureur G ON (C.codeGcAssureur = G.codeGcAssureur)
		INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND (a.id=B.idAdherent))
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
		AND (CASE WHEN 'FIDRA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='FIDRA' END)  
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete='vitalis') AND prt.codeActe='CS'
		AND prt.ententePrealable NOT IN ('2','9') AND B.okDecompte=1 AND B.valide=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,B.codePrestataire,C.libellePolice		 
	) depense GROUP BY codePrestataire
	
		-- DEBUT RECHERCHE BENEFS TRAITES CUMULES
UNION ALL
SELECT 3 AS ORDRE,'COUT MOYEN REEL "CS"' AS LIBELLE,codePrestataire AS CODEP,fn_libelle_prestataire(codePrestataire) AS PRESTATAIRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='1' THEN IFNULL(ROUND(SUM(JANVIER)/SUM(CJANVIER),0),0)  ELSE '0' END AS JANVIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='2' THEN IFNULL(ROUND(SUM(FEVRIER)/SUM(CFEVRIER),0),0)  ELSE '0' END AS FEVRIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='3' THEN IFNULL(ROUND(SUM(MARS)/SUM(CMARS),0),0)  ELSE '0' END AS MARS,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='4' THEN IFNULL(ROUND(SUM(AVRIL)/SUM(CAVRIL),0),0)  ELSE '0' END AS AVRIL,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='5' THEN IFNULL(ROUND(SUM(MAI)/SUM(CMAI),0),0) ELSE '0' END AS MAI,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='6' THEN IFNULL(ROUND(SUM(JUIN)/SUM(CJUIN),0),0) ELSE '0' END AS JUIN,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='7' THEN IFNULL(ROUND(SUM(JUILLET)/SUM(CJUILLET),0),0) ELSE '0' END AS JUILLET,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='8' THEN IFNULL(ROUND(SUM(AOUT)/SUM(CAOUT),0),0) ELSE '0' END AS AOUT,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='9' THEN IFNULL(ROUND(SUM(SEPTEMBRE)/SUM(CEPTEMBRE),0),0)  ELSE '0' END AS SEPTEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='10' THEN IFNULL(ROUND(SUM(OCTOBRE)/SUM(COCTOBRE),0),0)  ELSE '0' END AS OCTOBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='11' THEN IFNULL(ROUND(SUM(NOVEMBRE)/SUM(CNOVEMBRE),0),0)  ELSE '0' END AS NOVEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='12' THEN IFNULL(ROUND(SUM(DECEMBRE)/SUM(CDECEMBRE),0),0)  ELSE '0' END AS DECEMBRE,
	IFNULL(ROUND((SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT)+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE))/
	(SUM(CJANVIER)+SUM(CFEVRIER)+SUM(CMARS)+SUM(CAVRIL)+SUM(CMAI)+SUM(CJUIN)+SUM(CJUILLET)+SUM(CAOUT)+SUM(CEPTEMBRE)+SUM(COCTOBRE)+SUM(CNOVEMBRE)+SUM(CDECEMBRE)),0),0) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN SUM(prt.fraisReel) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN SUM(prt.fraisReel) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN SUM(prt.fraisReel) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN SUM(prt.fraisReel) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN SUM(prt.fraisReel) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN SUM(prt.fraisReel) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN SUM(prt.fraisReel) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN SUM(prt.fraisReel) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN SUM(prt.fraisReel) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN SUM(prt.fraisReel) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN SUM(prt.fraisReel) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN SUM(prt.fraisReel) ELSE '0' END 'DECEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COUNT(*) ELSE '0' END 'CJANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COUNT(*) ELSE '0' END 'CFEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COUNT(*) ELSE '0' END 'CMARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COUNT(*) ELSE '0' END 'CAVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COUNT(*) ELSE '0' END 'CMAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COUNT(*) ELSE '0' END 'CJUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COUNT(*) ELSE '0' END 'CJUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COUNT(*) ELSE '0' END 'CAOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COUNT(*) ELSE '0' END 'CEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COUNT(*) ELSE '0' END 'COCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COUNT(*) ELSE '0' END 'CNOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COUNT(*) ELSE '0' END 'CDECEMBRE'
		FROM p_prestationactes prt 
		INNER JOIN p_facture B ON (B.id=prt.idfacture)
		INNER JOIN police C ON (B.idPolice = C.id)
		INNER JOIN gc_assureur G ON (C.codeGcAssureur = G.codeGcAssureur)
		INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND (a.id=B.idAdherent))
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
		AND (CASE WHEN 'FIDRA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='FIDRA' END)  
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete='vitalis') AND prt.codeActe='CS'
		AND prt.ententePrealable NOT IN ('2','9') AND B.okDecompte=1 AND B.valide=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,B.codePrestataire,C.libellePolice		 
	) depense GROUP BY codePrestataire
UNION ALL	
	SELECT 4 AS ORDRE,'COUT MOYEN REMB "CS"' AS LIBELLE,codePrestataire AS CODEP,fn_libelle_prestataire(codePrestataire) AS PRESTATAIRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='1' THEN IFNULL(ROUND(SUM(JANVIER)/SUM(CJANVIER),0),0)  ELSE '0' END AS JANVIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='2' THEN IFNULL(ROUND(SUM(FEVRIER)/SUM(CFEVRIER),0),0)  ELSE '0' END AS FEVRIER,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='3' THEN IFNULL(ROUND(SUM(MARS)/SUM(CMARS),0),0)  ELSE '0' END AS MARS,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='4' THEN IFNULL(ROUND(SUM(AVRIL)/SUM(CAVRIL),0),0)  ELSE '0' END AS AVRIL,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='5' THEN IFNULL(ROUND(SUM(MAI)/SUM(CMAI),0),0) ELSE '0' END AS MAI,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='6' THEN IFNULL(ROUND(SUM(JUIN)/SUM(CJUIN),0),0) ELSE '0' END AS JUIN,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='7' THEN IFNULL(ROUND(SUM(JUILLET)/SUM(CJUILLET),0),0) ELSE '0' END AS JUILLET,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='8' THEN IFNULL(ROUND(SUM(AOUT)/SUM(CAOUT),0),0) ELSE '0' END AS AOUT,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='9' THEN IFNULL(ROUND(SUM(SEPTEMBRE)/SUM(CEPTEMBRE),0),0)  ELSE '0' END AS SEPTEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='10' THEN IFNULL(ROUND(SUM(OCTOBRE)/SUM(COCTOBRE),0),0)  ELSE '0' END AS OCTOBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='11' THEN IFNULL(ROUND(SUM(NOVEMBRE)/SUM(CNOVEMBRE),0),0)  ELSE '0' END AS NOVEMBRE,
	CASE WHEN MONTH(LEFT('2023-03-31',10))>='12' THEN IFNULL(ROUND(SUM(DECEMBRE)/SUM(CDECEMBRE),0),0)  ELSE '0' END AS DECEMBRE,
	IFNULL(ROUND((SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT)+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE))/
	(SUM(CJANVIER)+SUM(CFEVRIER)+SUM(CMARS)+SUM(CAVRIL)+SUM(CMAI)+SUM(CJUIN)+SUM(CJUILLET)+SUM(CAOUT)+SUM(CEPTEMBRE)+SUM(COCTOBRE)+SUM(CNOVEMBRE)+SUM(CDECEMBRE)),0),0) AS TOTAUX 
	 FROM (
		SELECT
			CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,C.codeGcAssureur,C.id,C.numeroPolice,C.libellePolice,B.codePrestataire,
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN SUM(prt.montantApayer) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN SUM(prt.montantApayer) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN SUM(prt.montantApayer) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN SUM(prt.montantApayer) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN SUM(prt.montantApayer) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN SUM(prt.montantApayer) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN SUM(prt.montantApayer) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN SUM(prt.montantApayer) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN SUM(prt.montantApayer) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN SUM(prt.montantApayer) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN SUM(prt.montantApayer) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN SUM(prt.montantApayer) ELSE '0' END 'DECEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='1' THEN COUNT(*) ELSE '0' END 'CJANVIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='2' THEN COUNT(*) ELSE '0' END 'CFEVRIER',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='3' THEN COUNT(*) ELSE '0' END 'CMARS',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='4' THEN COUNT(*) ELSE '0' END 'CAVRIL',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='5' THEN COUNT(*) ELSE '0' END 'CMAI',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='6' THEN COUNT(*) ELSE '0' END 'CJUIN',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='7' THEN COUNT(*) ELSE '0' END 'CJUILLET',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='8' THEN COUNT(*) ELSE '0' END 'CAOUT',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='9' THEN COUNT(*) ELSE '0' END 'CEPTEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='10' THEN COUNT(*) ELSE '0' END 'COCTOBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='11' THEN COUNT(*) ELSE '0' END 'CNOVEMBRE',
			CASE WHEN MONTH(LEFT(B.dateSysteme,10))='12' THEN COUNT(*) ELSE '0' END 'CDECEMBRE'
		FROM p_prestationactes prt 
		INNER JOIN p_facture B ON (B.id=prt.idfacture)
		INNER JOIN police C ON (B.idPolice = C.id)
		INNER JOIN gc_assureur G ON (C.codeGcAssureur = G.codeGcAssureur)
		INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND (a.id=B.idAdherent))
		WHERE (LEFT(B.dateSysteme,10) BETWEEN '2023-01-01' AND '2023-03-31')
		AND (CASE WHEN 'FIDRA' IS NULL THEN C.codeGcAssureur IS NOT NULL ELSE C.codeGcAssureur='FIDRA' END)  
		AND G.id!='45' AND C.idClient!='49' AND (B.codeSociete='vitalis') AND prt.codeActe='CS'
		AND prt.ententePrealable NOT IN ('2','9') AND B.okDecompte=1 AND B.valide=1
		AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
		GROUP BY B.dateSysteme,B.codeMois,B.codeExercice,C.codeGcAssureur,C.id,C.numeroPolice,B.codePrestataire,C.libellePolice		 
	) depense GROUP BY codePrestataire
	
)  fidra
-- where codePrestataire='CE0014'
ORDER BY CODEP,ORDRE 