set @start_date= '2016-07-01';
set @end_date = '2018-07-31';
select count(id_patient) as total_positif, count(infant) as dead_abandon,
count(followup) as muac_followup, count(odk) as muac_odk,
sum(if(followup is not null or odk is not null,1,0)) as with_muac, sum(if(followup is not null and odk is not null,1,0)) as overlap
from(
select id_patient, infant,
followup,odk
from(
select testing_specimen.id_patient from testing_specimen where timestampdiff(month,date_of_birth,@start_date)<60 and timestampdiff(month,date_of_birth,@end_date)>=6 and pcr_result=1
union
select testing_result.id_patient from testing_result left join tracking_infant on tracking_infant.id_patient=testing_result.id_patient where timestampdiff(month,dob,@start_date)<60 and timestampdiff(month,dob,@end_date)>=6 and result=1
union
select tracking_followup.id_patient from tracking_followup left join tracking_infant on tracking_infant.id_patient=tracking_followup.id_patient where timestampdiff(month,dob,@start_date)<60 and timestampdiff(month,dob,@end_date)>=6
)j
left join
(select distinct tracking_followup.id_patient as followup from tracking_followup where mid_arm_circumference >0)o
on followup = j.id_patient
left join
(
SELECT distinct patient.id as odk FROM openfn.odk_child_visit left join caris_db.patient on patient.patient_code=odk_child_visit.patient_code
where muac>0 and patient.id is not null
)e
on j.id_patient=odk
left join
(select distinct id_patient as infant from tracking_infant where is_dead=1 or is_abandoned=1
)xy on j.id_patient=infant
#group by id_patient
)juo
Comments