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
CAST('Frais d''hopitaux' AS Varchar(50)) AS TypeDepense,
year(TblPolicyClaim.DischargeDate) AS "Year",
TblPolicy.PolicyNr,
TblPolicyClaim.Disease,
TblPolicyClaim.DiseaseType,
TblPolicyClaim.DiseaseSubTyp,
TblClaimDetails.Hospital,
SUM(TblClaimDetails.ExpensesBilled) AS SumExpenses,
SUM(TblClaimDetails.AmountClaimable) AS AmountClaimable
FROM
TblClaimDetails INNER JOIN
TblPolicyClaim ON TblClaimDetails.ClaimID = TblPolicyClaim.ClaimID INNER JOIN
TblPolicy ON TblPolicyClaim.PolicyID = TblPolicy.PolicyID
WHERE
YEAR(TblPolicyClaim.DischargeDate)>=2008
AND TblPolicy.PolicyNr LIKE 'PE%'
AND TblClaimDetails.ClaimExpenseType IN ('Frais de lit', 'Frais de nourriture')
GROUP BY
TblPolicyClaim.DischargeDate,
TblPolicy.PolicyNr,
TblPolicyClaim.Disease,
TblPolicyClaim.DiseaseType,
TblPolicyClaim.DiseaseSubTyp,
TblClaimDetails.Hospital,
TblPolicy.PolicyNr
UNION ALL
SELECT
CAST('Personnels médicaux' AS Varchar(50)),
year(TblPolicyClaim.DischargeDate),
TblPolicy.PolicyNr,
TblPolicyClaim.Disease,
TblPolicyClaim.DiseaseType,
TblPolicyClaim.DiseaseSubTyp,
TblClaimDetails.Hospital,
SUM(TblClaimDetails.ExpensesBilled),
SUM(TblClaimDetails.AmountClaimable)
FROM
TblClaimDetails INNER JOIN
TblPolicyClaim ON TblClaimDetails.ClaimID = TblPolicyClaim.ClaimID INNER JOIN
TblPolicy ON TblPolicyClaim.PolicyID = TblPolicy.PolicyID
WHERE
YEAR(TblPolicyClaim.DischargeDate)>=2008
AND TblPolicy.PolicyNr LIKE 'PE%'
AND TblClaimDetails.ClaimExpenseType IN ('Frais de médecin', 'Frais d''infirmiers', 'Frais de kiné')
GROUP BY
TblPolicyClaim.DischargeDate,
TblPolicy.PolicyNr,
TblPolicyClaim.Disease,
TblPolicyClaim.DiseaseType,
TblPolicyClaim.DiseaseSubTyp,
TblClaimDetails.Hospital,
TblPolicy.PolicyNr
ORDER BY
TblPolicyClaim.DischargeDate,
TblPolicy.PolicyNr |
Partager