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
Comments