SELECT Breakdown,
SUM(IF(Result='Completed',1,0)) AS Completed,
SUM(IF(Result='Refused',1,0)) AS Refused,
SUM(IF(Result='Ineligible',1,0)) AS Ineligible,
COUNT(*) AS Number_of_{group},
CONCAT(TRUNCATE((( COUNT(Flu_Shot.demo_no) / COUNT(*) )*100),0),'%') AS Status_Recorded
FROM (SELECT Breakdown,
demographic.demographic_no,
YEAR(DATE('2014-10-01'))-demographic.year_of_birth-(DATE_FORMAT(DATE('2014-10-01'),'00-%m-%d')= DATE('2014-10-01') OR c_finalEDB >= DATE('2014-10-01'))
AND (pg1_menLMP < DATE('2014-10-01') OR pg1_menLMP IS NULL)
) AS Pregnant
ON Patient.demographic_no = Pregnant.demographic_no
LEFT JOIN (SELECT demo_no,
MIN(Result) AS Result
FROM
(SELECT preventions.demographic_no AS demo_no,
CASE refused
WHEN 0 THEN 'Completed'
WHEN 1 THEN 'Refused'
WHEN 2 THEN 'Ineligible'
END AS Result
FROM preventions
WHERE preventions.prevention_type = 'FLU'
AND preventions.deleted = 0
AND preventions.prevention_date BETWEEN DATE('2014-09-20') AND DATE('2015-01-31')
UNION
SELECT billing_on_cheader1.demographic_no AS demo_no,
'Completed' AS Result
FROM billing_on_cheader1,
(SELECT ch1_id
FROM billing_on_item
WHERE billing_on_item.service_code IN ('G591A','G590A','_G590A','Q691A','Q690A','__G590A','Q130A')
AND billing_on_item.status <> 'D'
AND billing_on_item.service_date BETWEEN DATE('2014-09-20') AND DATE('2015-01-31')
) AS Item
WHERE billing_on_cheader1.id = Item.ch1_id
AND billing_on_cheader1.status <> 'D'
) AS temp
GROUP BY demo_no
) As Flu_Shot
ON Flu_Shot.demo_no = Patient.demographic_no
WHERE ('{group}' = 'All_Patients'
OR
('{group}' = 'Older_65' AND Patient.Age >= 65)
OR
('{group}' = 'Diabetic_Patients' AND Diabetes.demographic_no IS NOT NULL)
OR
('{group}' = 'Asthmatic_Patients' AND Asthma.demographic_no IS NOT NULL)
OR
('{group}' = 'Pregnant_Patients' AND Pregnant.demographic_no IS NOT NULL)
)
GROUP BY Breakdown WITH ROLLUP
;
All Patients
65 or Older (rostered)
Pregnant Patients
Diabetic Patients
Asthmatic/COPD Patients
MRP
Team