1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
|
SELECT
Nom_agce,
SUM (Cout_intervention) AS coût_tot_Intervention,
SUM (somme_prise_en_charge) AS Prise_en_charge,
SUM (Reste_à_payer) AS Somme_à_payer,
SUM (cout_remise) AS Remise,
SUM (total_à_payer) AS Total_reçu
FROM
(
SELECT
Compagni_assur.IDcomp AS IDcomp_assur,
Compagni_assur.Nom_compagnie AS Nom_compagnie,
Agences_assur.IDAgences AS IDAgences,
Agences_assur.Nom_agce AS Nom_agce,
Praticiens.IDPraticients AS IDPraticients,
Praticiens.Nom_praticien AS Nom_praticien,
Patients.IDPatients AS IDPatients,
(Patients.Prenoms_patient + Patients.Nom_patient ) AS Prenoms_patient,
Interventions.IDIntervention AS IDIntervention,
Interventions.IDActes AS IDActes,
Actes.Lib_acte AS Designation,
Interventions.Date_intervention AS DATE_inter,
Interventions.Coût_unitaire AS Coût_unitaire,
Interventions.Quantité AS Quantité,
Interventions.Prise_en_charge AS Prise_en_charge,
Interventions.Coût_unitaire * Interventions.Quantité AS Cout_intervention,
((Interventions.Coût_unitaire * Interventions.Quantité)* Interventions.Prise_en_charge)/100 AS somme_prise_en_charge,
(Interventions.Coût_unitaire * Interventions.Quantité)-(((Interventions.Coût_unitaire * Interventions.Quantité)* Interventions.Prise_en_charge)/100) AS Reste_à_payer,
Interventions.Remise AS tax_Remise,
(((Interventions.Coût_unitaire * Interventions.Quantité)-((Interventions.Coût_unitaire * Interventions.Quantité) * Interventions.Prise_en_charge)/100) * Interventions.Remise)/100 AS cout_remise,
((Interventions.Coût_unitaire * Interventions.Quantité)-((Interventions.Coût_unitaire * Interventions.Quantité) * Interventions.Prise_en_charge)/100)- ((((Interventions.Coût_unitaire * Interventions.Quantité)-((Interventions.Coût_unitaire * Interventions.Quantité) * Interventions.Prise_en_charge)/100) * Interventions.Remise)/100) AS total_à_payer
FROM
Compagni_assur,
Agences_assur,
Interventions,
Actes,
Patients,
Praticiens
WHERE
Patients.IDPatients = Interventions.IDPatients
AND Actes.IDActes = Interventions.IDActes
AND Praticiens.IDPraticients= Interventions.IDPraticients
AND Agences_assur.IDAgences = Interventions.IDAgences
AND Compagni_assur.IDcomp = Agences_assur.IDcomp
AND (Interventions.Date_intervention BETWEEN {periode_deb} AND {periode_fin})
AND Compagni_assur.IDcomp = {idcomp}
)
WHERE
DATE_inter BETWEEN {deb_periode} AND {fin_periode}
AND IDcomp_assur = {nomcomp}
GROUP BY
Nom_agce
ORDER BY
Nom_agce DESC |
Partager