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, 
sum(if(gender = 1,if(timestampdiff(month,dob,date)<12 and timestampdiff(month,dob,date)>=6,1,0),0)) as m_6_11,
sum(if(gender = 2,if(timestampdiff(month,dob,date)<12 and timestampdiff(month,dob,date)>=6,1,0),0)) as f_6_11,
sum(if(gender = 1,if(timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=12,1,0),0)) as m_12_59,
sum(if(gender = 2,if(timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=12,1,0),0)) as f_12_59

from
(
select distinct patient_code, dob, gender, date 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, dob, gender, date_of_visit as date 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, infant_dob as dob, infant_gender as gender, date 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)