USE caris_db;
SET @start_date= "2014-10-01";
SET @end_date="2015-09-30";

SELECT b.patient_surveyed,a.malnourished,
    a.malnourished_btwn_0_11_mnths,a.malnourished_btwn_12_60_mnths, b.male, b.female
    FROM(
    SELECT 
                    patient_code,
                    CASE
                        WHEN (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight') THEN "Yes"
                        ELSE "no"
                    END AS malnourished,
                    CASE
                        WHEN
                            age_in_months >= 6
                                AND age_in_months <= 11
                                AND (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight')
                        THEN "Yes"
                        ELSE "no"
                    END AS malnourished_btwn_0_11_mnths,
                    CASE
                        WHEN
                            age_in_months >= 12
                                AND age_in_months <= 60
                                AND (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight')
                        THEN "Yes"
                        ELSE "no"
                    END AS malnourished_btwn_12_60_mnths
							
        FROM(

        SELECT * FROM (
            SELECT a.*,
                     CASE
                        WHEN age_in_months = 0 THEN IF(infant_weight < 2, 'surunderweight', IF(infant_weight < 2.4, 'underweight', ''))
                        WHEN age_in_months > 0 AND age_in_months <= 6 THEN
                            IF((infant_weight - 2) / (age_in_months - 0) < ((4.6 - 2) / (6 - 0)), 'surunderweight',
                            IF((infant_weight - 2.4) / (age_in_months - 0) < ((5.5 - 2.4) / (6 - 0)), 'underweight', ''))
                                WHEN
                                    age_in_months > 6
                                        AND age_in_months <= 12
                                THEN
                                    IF((infant_weight - 4.6) / (age_in_months - 6) < ((6.5- 4.6 ) / (12 - 6)), 'surunderweight',
                                        IF((infant_weight - 5.5) / (age_in_months - 6) < ((7.8 - 5.5) / (12 - 6)), 'underweight', ''))
                                WHEN
                                    age_in_months > 12
                                        AND age_in_months <= 18
                                THEN
                                    IF((infant_weight - 6.5) / (age_in_months - 12) < ((7.3- 6.5) / (18 - 12)), 'surunderweight',
                                        IF((infant_weight - 7.8) / (age_in_months - 12) < ((8.7 - 7.8) / (18 - 12)), 'underweight', ''))
                                WHEN
                                    age_in_months > 18
                                        AND age_in_months <= 24
                                THEN
                                    IF((infant_weight - 7.3) / (age_in_months - 18) < ((8.1 - 7.3) / (24 - 18)), 'surunderweight',
                                        IF((infant_weight - 8.7) / (age_in_months - 18) < ((9.6 - 8.7) / (24 - 18)), 'underweight', ''))
                                WHEN
                                    age_in_months > 24
                                        AND age_in_months <= 30
                                THEN
                                    IF((infant_weight - 8.1) / (age_in_months - 24) < ((9 - 8.1 ) / (30 - 24)), 'surunderweight',
                                        IF((infant_weight - 9.6) / (age_in_months - 24) < ((10.5 - 9.6) / (30 - 24)), 'underweight', ''))
                                WHEN
                                    age_in_months > 30
                                        AND age_in_months <= 36
                                THEN
                                    IF((infant_weight - 9) / (age_in_months - 30) < ((9.8 - 9) / (36 - 30)), 'surunderweight',
                                        IF((infant_weight - 10.5) / (age_in_months - 30) < ((11.4 - 10.5) / (36 - 30)), 'underweight', ''))
                                WHEN
                                    age_in_months > 36
                                        AND age_in_months <= 42
                                THEN
                    				IF((infant_weight - 9.8) / (age_in_months - 36) < ((10.3 - 9.8) / (42 - 36)), 'surunderweight',
                    					IF((infant_weight - 11.4) / (age_in_months - 36) < ((12 - 11.4) / (42 - 36)), 'underweight', ''))
                                WHEN
                                    age_in_months > 42
                                        AND age_in_months <= 48
                                THEN
                                    IF((infant_weight - 10.3) / (age_in_months - 42) < ((10.9 - 10.3) / (48 - 42)), 'surunderweight',
                                        IF((infant_weight - 12) / (age_in_months - 42) < ((12.8 - 12) / (48 - 42)), 'underweight', ''))
                                WHEN
                                    age_in_months > 48
                                        AND age_in_months <= 54
                                THEN
                                    IF((infant_weight - 10.9) / (age_in_months - 48) < ((11.5 - 10.9 ) / (54 - 48)), 'surunderweight',
                                        IF((infant_weight - 12.8) / (age_in_months - 48) < ((13.5 - 12.8) / (54 - 48)), 'underweight', ''))
                                WHEN
                                    age_in_months > 54
                                        AND age_in_months <= 60
                                THEN
                                    IF((infant_weight - 11.5) / (age_in_months - 54) < ((12 - 11.5 ) / (60 - 54)), 'surunderweight',
                                        IF((infant_weight - 13.5) / (age_in_months - 54) < ((14.2 - 13.5) / (60 - 54)), 'underweight', ''))
                            END AS nutrition_status
        FROM (
                SELECT gender as infant_gender, patient_code as patient_code, death_date, first_name,last_name, 
                        f_date AS followup_date,f_weight AS infant_weight,f_height AS followup_height,patient.linked_to_id_patient as link,
                        patient.id AS id_patient,TIMESTAMPDIFF(MONTH,dob,f_date) AS age_in_months,dob 
                    FROM (
                        SELECT DATE AS f_date,weight AS f_weight,height AS f_height,id_patient,created_at AS f_created_at
                            FROM tracking_followup AS a WHERE a.date BETWEEN @start_date AND @end_date ) f
                LEFT JOIN tracking_infant t ON t.id_patient=f.id_patient 
                LEFT JOIN patient ON patient.id=f.id_patient  WHERE (not(t.death_date<@start_date)||(death_date='0000-00-00')) AND f_weight<>0) a 
        WHERE(a.age_in_months BETWEEN 6 AND 60) )b 
        where(nutrition_status='underweight' or nutrition_status='surunderweight') group by id_patient) malnou ) a
RIGHT JOIN (
        select patient_code as patient_surveyed, male, female
            from(
                select i.dob,
                CASE
                        WHEN
                            i.gender = 2
                        THEN "Yes"
                        ELSE "no"
                    END AS female,
				CASE
                        WHEN
                            i.gender = 1
                        THEN "Yes"
                        ELSE "no"
                    END AS male,
                
                f.*,p.patient_code as patient_code from tracking_followup f left join tracking_infant i on i.id_patient=f.id_patient 
                left join patient p on p.id=f.id_patient
                where (TIMESTAMPDIFF(MONTH,i.dob,f.date) between 0 and 60) && weight<>0 
                and f.date between @start_date and @end_date group by f.id_patient) c 
                
    ) b on a.patient_code=b.patient_surveyed