API tools faq
paste
jeniferfleurant

OVC_Nut_by_patient

jeniferfleurant
May 4th, 2016
305
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.89 KB | None | 0 0
raw download clone embed print report
  1. USE caris_db;
  2. SET @start_date= "2014-10-01";
  3. SET @end_date="2015-09-30";
  4.  
  5. SELECT b.patient_surveyed,a.malnourished,
  6. a.malnourished_btwn_0_11_mnths,a.malnourished_btwn_12_60_mnths, b.male, b.female
  7. FROM(
  8. SELECT
  9. patient_code,
  10. CASE
  11. WHEN (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight') THEN "Yes"
  12. ELSE "no"
  13. END AS malnourished,
  14. CASE
  15. WHEN
  16. age_in_months >= 6
  17. AND age_in_months <= 11
  18. AND (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight')
  19. THEN "Yes"
  20. ELSE "no"
  21. END AS malnourished_btwn_0_11_mnths,
  22. CASE
  23. WHEN
  24. age_in_months >= 12
  25. AND age_in_months <= 60
  26. AND (nutrition_status = 'underweight' OR nutrition_status = 'surunderweight')
  27. THEN "Yes"
  28. ELSE "no"
  29. END AS malnourished_btwn_12_60_mnths
  30.  
  31. FROM(
  32.  
  33. SELECT * FROM (
  34. SELECT a.*,
  35. CASE
  36. WHEN age_in_months = 0 THEN IF(infant_weight < 2, 'surunderweight', IF(infant_weight < 2.4, 'underweight', ''))
  37. WHEN age_in_months > 0 AND age_in_months <= 6 THEN
  38. IF((infant_weight - 2) / (age_in_months - 0) < ((4.6 - 2) / (6 - 0)), 'surunderweight',
  39. IF((infant_weight - 2.4) / (age_in_months - 0) < ((5.5 - 2.4) / (6 - 0)), 'underweight', ''))
  40. WHEN
  41. age_in_months > 6
  42. AND age_in_months <= 12
  43. THEN
  44. IF((infant_weight - 4.6) / (age_in_months - 6) < ((6.5- 4.6 ) / (12 - 6)), 'surunderweight',
  45. IF((infant_weight - 5.5) / (age_in_months - 6) < ((7.8 - 5.5) / (12 - 6)), 'underweight', ''))
  46. WHEN
  47. age_in_months > 12
  48. AND age_in_months <= 18
  49. THEN
  50. IF((infant_weight - 6.5) / (age_in_months - 12) < ((7.3- 6.5) / (18 - 12)), 'surunderweight',
  51. IF((infant_weight - 7.8) / (age_in_months - 12) < ((8.7 - 7.8) / (18 - 12)), 'underweight', ''))
  52. WHEN
  53. age_in_months > 18
  54. AND age_in_months <= 24
  55. THEN
  56. IF((infant_weight - 7.3) / (age_in_months - 18) < ((8.1 - 7.3) / (24 - 18)), 'surunderweight',
  57. IF((infant_weight - 8.7) / (age_in_months - 18) < ((9.6 - 8.7) / (24 - 18)), 'underweight', ''))
  58. WHEN
  59. age_in_months > 24
  60. AND age_in_months <= 30
  61. THEN
  62. IF((infant_weight - 8.1) / (age_in_months - 24) < ((9 - 8.1 ) / (30 - 24)), 'surunderweight',
  63. IF((infant_weight - 9.6) / (age_in_months - 24) < ((10.5 - 9.6) / (30 - 24)), 'underweight', ''))
  64. WHEN
  65. age_in_months > 30
  66. AND age_in_months <= 36
  67. THEN
  68. IF((infant_weight - 9) / (age_in_months - 30) < ((9.8 - 9) / (36 - 30)), 'surunderweight',
  69. IF((infant_weight - 10.5) / (age_in_months - 30) < ((11.4 - 10.5) / (36 - 30)), 'underweight', ''))
  70. WHEN
  71. age_in_months > 36
  72. AND age_in_months <= 42
  73. THEN
  74. IF((infant_weight - 9.8) / (age_in_months - 36) < ((10.3 - 9.8) / (42 - 36)), 'surunderweight',
  75. IF((infant_weight - 11.4) / (age_in_months - 36) < ((12 - 11.4) / (42 - 36)), 'underweight', ''))
  76. WHEN
  77. age_in_months > 42
  78. AND age_in_months <= 48
  79. THEN
  80. IF((infant_weight - 10.3) / (age_in_months - 42) < ((10.9 - 10.3) / (48 - 42)), 'surunderweight',
  81. IF((infant_weight - 12) / (age_in_months - 42) < ((12.8 - 12) / (48 - 42)), 'underweight', ''))
  82. WHEN
  83. age_in_months > 48
  84. AND age_in_months <= 54
  85. THEN
  86. IF((infant_weight - 10.9) / (age_in_months - 48) < ((11.5 - 10.9 ) / (54 - 48)), 'surunderweight',
  87. IF((infant_weight - 12.8) / (age_in_months - 48) < ((13.5 - 12.8) / (54 - 48)), 'underweight', ''))
  88. WHEN
  89. age_in_months > 54
  90. AND age_in_months <= 60
  91. THEN
  92. IF((infant_weight - 11.5) / (age_in_months - 54) < ((12 - 11.5 ) / (60 - 54)), 'surunderweight',
  93. IF((infant_weight - 13.5) / (age_in_months - 54) < ((14.2 - 13.5) / (60 - 54)), 'underweight', ''))
  94. END AS nutrition_status
  95. FROM (
  96. SELECT gender as infant_gender, patient_code as patient_code, death_date, first_name,last_name,
  97. f_date AS followup_date,f_weight AS infant_weight,f_height AS followup_height,patient.linked_to_id_patient as link,
  98. patient.id AS id_patient,TIMESTAMPDIFF(MONTH,dob,f_date) AS age_in_months,dob
  99. FROM (
  100. SELECT DATE AS f_date,weight AS f_weight,height AS f_height,id_patient,created_at AS f_created_at
  101. FROM tracking_followup AS a WHERE a.date BETWEEN @start_date AND @end_date ) f
  102. LEFT JOIN tracking_infant t ON t.id_patient=f.id_patient
  103. 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
  104. WHERE(a.age_in_months BETWEEN 6 AND 60) )b
  105. where(nutrition_status='underweight' or nutrition_status='surunderweight') group by id_patient) malnou ) a
  106. RIGHT JOIN (
  107. select patient_code as patient_surveyed, male, female
  108. from(
  109. select i.dob,
  110. CASE
  111. WHEN
  112. i.gender = 2
  113. THEN "Yes"
  114. ELSE "no"
  115. END AS female,
  116. CASE
  117. WHEN
  118. i.gender = 1
  119. THEN "Yes"
  120. ELSE "no"
  121. END AS male,
  122.  
  123. f.*,p.patient_code as patient_code from tracking_followup f left join tracking_infant i on i.id_patient=f.id_patient
  124. left join patient p on p.id=f.id_patient
  125. where (TIMESTAMPDIFF(MONTH,i.dob,f.date) between 0 and 60) && weight<>0
  126. and f.date between @start_date and @end_date group by f.id_patient) c
  127.  
  128. ) b on a.patient_code=b.patient_surveyed
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!