nut 2017 numerator (muac/weigh)

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


select left(patient_code, 8) as hospital, sum(gender=1) as total_male, sum(gender=2) as total_female, sum(gender!=1 and gender!=2) as total_unknown, sum(age_in_month between 6 and 11) as '6-11', sum(age_in_month between 12 and 59) as '12-59' from
(
select patient_code, age_in_month, CASE
                        WHEN age_in_month = 0 THEN IF(weight < 2, 'surunderweight', IF(weight < 2.4, 'underweight', ''))
                        WHEN age_in_month > 0 AND age_in_month <= 6 THEN
                            IF((weight - 2) / (age_in_month - 0) < ((4.6 - 2) / (6 - 0)), 'surunderweight',
                            IF((weight - 2.4) / (age_in_month - 0) < ((5.5 - 2.4) / (6 - 0)), 'underweight', ''))
                                WHEN
                                    age_in_month > 6
                                        AND age_in_month <= 12
                                THEN
                                    IF((weight - 4.6) / (age_in_month - 6) < ((6.5- 4.6 ) / (12 - 6)), 'surunderweight',
                                        IF((weight - 5.5) / (age_in_month - 6) < ((7.8 - 5.5) / (12 - 6)), 'underweight', ''))
                                WHEN
                                    age_in_month > 12
                                        AND age_in_month <= 18
                                THEN
                                    IF((weight - 6.5) / (age_in_month - 12) < ((7.3- 6.5) / (18 - 12)), 'surunderweight',
                                        IF((weight - 7.8) / (age_in_month - 12) < ((8.7 - 7.8) / (18 - 12)), 'underweight', ''))
                                WHEN
                                    age_in_month > 18
                                        AND age_in_month <= 24
                                THEN
                                    IF((weight - 7.3) / (age_in_month - 18) < ((8.1 - 7.3) / (24 - 18)), 'surunderweight',
                                        IF((weight - 8.7) / (age_in_month - 18) < ((9.6 - 8.7) / (24 - 18)), 'underweight', ''))
                                WHEN
                                    age_in_month > 24
                                        AND age_in_month <= 30
                                THEN
                                    IF((weight - 8.1) / (age_in_month - 24) < ((9 - 8.1 ) / (30 - 24)), 'surunderweight',
                                        IF((weight - 9.6) / (age_in_month - 24) < ((10.5 - 9.6) / (30 - 24)), 'underweight', ''))
                                WHEN
                                    age_in_month > 30
                                        AND age_in_month <= 36
                                THEN
                                    IF((weight - 9) / (age_in_month - 30) < ((9.8 - 9) / (36 - 30)), 'surunderweight',
                                        IF((weight - 10.5) / (age_in_month - 30) < ((11.4 - 10.5) / (36 - 30)), 'underweight', ''))
                                WHEN
                                    age_in_month > 36
                                        AND age_in_month <= 42
                                THEN
                                    IF((weight - 9.8) / (age_in_month - 36) < ((10.3 - 9.8) / (42 - 36)), 'surunderweight',
                                        IF((weight - 11.4) / (age_in_month - 36) < ((12 - 11.4) / (42 - 36)), 'underweight', ''))
                                WHEN
                                    age_in_month > 42
                                        AND age_in_month <= 48
                                THEN
                                    IF((weight - 10.3) / (age_in_month - 42) < ((10.9 - 10.3) / (48 - 42)), 'surunderweight',
                                        IF((weight - 12) / (age_in_month - 42) < ((12.8 - 12) / (48 - 42)), 'underweight', ''))
                                WHEN
                                    age_in_month > 48
                                        AND age_in_month <= 54
                                THEN
                                    IF((weight - 10.9) / (age_in_month - 48) < ((11.5 - 10.9 ) / (54 - 48)), 'surunderweight',
                                        IF((weight - 12.8) / (age_in_month - 48) < ((13.5 - 12.8) / (54 - 48)), 'underweight', ''))
                                WHEN
                                    age_in_month > 54
                                        AND age_in_month <= 60
                                THEN
                                    IF((weight - 11.5) / (age_in_month - 54) < ((12 - 11.5 ) / (60 - 54)), 'surunderweight',
                                        IF((weight - 13.5) / (age_in_month - 54) < ((14.2 - 13.5) / (60 - 54)), 'underweight', ''))
                            END AS nutrition_status, gender
from
(
select patient_code, weight, age_in_month, gender from(
select patient_code, infant_weight as weight, timestampdiff(month,infant_dob,date) as age_in_month,infant_gender as gender 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
union
select distinct patient_code, weight, timestampdiff(month,dob,date) as age_in_month, gender from tracking_followup f1 left join patient on patient.id=f1.id_patient
left join tracking_infant on patient.id=tracking_infant.id_patient
 where date between @start_date and @end_date and weight>0 and timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=6
 and weight = (select min(weight) from tracking_followup f2 where f1.id_patient=f2.id_patient)
union
select distinct o1.patient_code, weight_kg as weight, timestampdiff(month,dob,date_of_visit) as age_in_month, gender from openfn.odk_child_visit o1
left join patient on patient.patient_code=o1.patient_code
left join tracking_infant on patient.id=id_patient
where
date_of_visit between @start_date and @end_date and weight_kg>0 and timestampdiff(month,dob,date_of_visit)<60 and timestampdiff(month,dob,date_of_visit)>=6
and weight_kg = (select min(weight_kg) from openfn.odk_child_visit o2 where o1.patient_code=o2.patient_code)
)code1
    where code1.weight=(
    select min(weight) from(
    select patient_code, infant_weight as weight, infant_gender, timestampdiff(month,infant_dob,date) as age_in_month 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
    union
    select distinct patient_code, weight, gender, timestampdiff(month,dob,date) as age_in_month from tracking_followup f1 left join patient on patient.id=f1.id_patient
    left join tracking_infant on patient.id=tracking_infant.id_patient
     where date between @start_date and @end_date and weight>0 and timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=6
     and weight = (select min(weight) from tracking_followup f2 where f1.id_patient=f2.id_patient)
    union
    select distinct o1.patient_code, weight_kg as weight, gender, timestampdiff(month,dob,date_of_visit) as age_in_month from openfn.odk_child_visit o1
    left join patient on patient.patient_code=o1.patient_code
    left join tracking_infant on patient.id=id_patient
    where
    date_of_visit between @start_date and @end_date and weight_kg>0 and timestampdiff(month,dob,date_of_visit)<60 and timestampdiff(month,dob,date_of_visit)>=6
    and weight_kg = (select min(weight_kg) from openfn.odk_child_visit o2 where o1.patient_code=o2.patient_code)
    )code2
    where code2.patient_code=code1.patient_code
    )
)as end_of_weight
union
select patient_code, age_in_month, 'surunderweight' as nutrition_status, gender from
(select distinct patient_code, timestampdiff(month,dob,date) as age_in_month, gender 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 '2016-10-01' and '2017-09-30' and mid_arm_circumference>3 and mid_arm_circumference<=12.5
 and timestampdiff(month,dob,date)<60 and timestampdiff(month,dob,date)>=6
union
select distinct odk_child_visit.patient_code, timestampdiff(month,dob,date_of_visit) as age_in_month, gender 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 '2016-10-01' and '2017-09-30' and (muac>3) and muac<=12.5
and timestampdiff(month,dob,date_of_visit)<60 and timestampdiff(month,dob,date_of_visit)>=6
)a
)as end
where nutrition_status='surunderweight'

group by hospital