USE caris_db;
SET @start_date= '2016-10-01';
SET @end_date='2017-09-30';

select left(patient_code,8) as hospital, 
count(*) as total from
(
select distinct patient_code from tracking_followup left join patient on patient.id=tracking_followup.id_patient
left join tracking_infant on patient.id=tracking_infant.id_patient
 where date between @start_date and @end_date and (mid_arm_circumference>0 or weight>0) and timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=6
union
select distinct odk_child_visit.patient_code from openfn.odk_child_visit
left join patient on patient.patient_code=odk_child_visit.patient_code
left join tracking_infant on patient.id=id_patient
where 
date_of_visit between @start_date and @end_date and (muac>0 or weight_kg>0) and timestampdiff(month,dob,date_of_visit)<60 and timestampdiff(month,dob,date_of_visit)>=6
union
select patient_code from testing_mereenfant 
left join testing_specimen on testing_specimen.id_patient=testing_mereenfant.id_patient
left join patient on patient.id=testing_mereenfant.id_patient
where pcr_result=1 and infant_weight>0 and date between @start_date and @end_date and timestampdiff(month,infant_dob,date)<60 and timestampdiff(month,infant_dob,date)>=6
)a
group by left(patient_code,8)