API tools faq
paste
Advertisement
capiro

Nutrition script version 2 to review

capiro
Oct 28th, 2016
107
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 15.02 KB | None | 0 0
raw download clone embed print report diff
  1. USE caris_db;
  2. SET @start_date= "2015-10-01";
  3. SET @end_date="2016-09-30";
  4.  
  5. SELECT b.patient_surveyed,a.malnourished,
  6.     a.malnourished_btwn_6_11_mnths,a.malnourished_btwn_12_60_mnths,a.malnourished_btwn_6_11_mnths_from_mereenfant,a.malnourished_btwn_12_60_mnths_from_mereenfant,nutrition_status_from_mereenfant, male, 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_6_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.                     # mereenfant
  31.                     CASE
  32.                         WHEN (nutrition_status_from_mereenfant = 'underweight' OR nutrition_status_from_mereenfant = 'surunderweight') THEN "Yes"
  33.                         ELSE "no"
  34.                     END AS malnourished_from_mereenfant,
  35.                     CASE
  36.                         WHEN
  37.                             age_in_month_me >= 6
  38.                                 AND age_in_month_me <= 11
  39.                                 AND (nutrition_status_from_mereenfant = 'underweight' OR nutrition_status_from_mereenfant = 'surunderweight')
  40.                         THEN "Yes"
  41.                         ELSE "no"
  42.                     END AS malnourished_btwn_6_11_mnths_from_mereenfant,
  43.                     CASE
  44.                         WHEN
  45.                             age_in_month_me >= 12
  46.                                 AND age_in_month_me <= 60
  47.                                 AND (nutrition_status_from_mereenfant = 'underweight' OR nutrition_status_from_mereenfant = 'surunderweight')
  48.                         THEN "Yes"
  49.                         ELSE "no"
  50.                     END AS malnourished_btwn_12_60_mnths_from_mereenfant,
  51.                     nutrition_status_from_mereenfant
  52.                            
  53.         FROM(
  54.  
  55.         SELECT * FROM (
  56.             SELECT a.*,
  57.                      CASE
  58.                         WHEN age_in_months = 0 THEN IF(infant_weight < 2, 'surunderweight', IF(infant_weight < 2.4, 'underweight', ''))
  59.                         WHEN age_in_months > 0 AND age_in_months <= 6 THEN
  60.                             IF((infant_weight - 2) / (age_in_months - 0) < ((4.6 - 2) / (6 - 0)), 'surunderweight',
  61.                             IF((infant_weight - 2.4) / (age_in_months - 0) < ((5.5 - 2.4) / (6 - 0)), 'underweight', ''))
  62.                                 WHEN
  63.                                     age_in_months > 6
  64.                                         AND age_in_months <= 12
  65.                                 THEN
  66.                                     IF((infant_weight - 4.6) / (age_in_months - 6) < ((6.5- 4.6 ) / (12 - 6)), 'surunderweight',
  67.                                         IF((infant_weight - 5.5) / (age_in_months - 6) < ((7.8 - 5.5) / (12 - 6)), 'underweight', ''))
  68.                                 WHEN
  69.                                     age_in_months > 12
  70.                                         AND age_in_months <= 18
  71.                                 THEN
  72.                                     IF((infant_weight - 6.5) / (age_in_months - 12) < ((7.3- 6.5) / (18 - 12)), 'surunderweight',
  73.                                         IF((infant_weight - 7.8) / (age_in_months - 12) < ((8.7 - 7.8) / (18 - 12)), 'underweight', ''))
  74.                                 WHEN
  75.                                     age_in_months > 18
  76.                                         AND age_in_months <= 24
  77.                                 THEN
  78.                                     IF((infant_weight - 7.3) / (age_in_months - 18) < ((8.1 - 7.3) / (24 - 18)), 'surunderweight',
  79.                                         IF((infant_weight - 8.7) / (age_in_months - 18) < ((9.6 - 8.7) / (24 - 18)), 'underweight', ''))
  80.                                 WHEN
  81.                                     age_in_months > 24
  82.                                         AND age_in_months <= 30
  83.                                 THEN
  84.                                     IF((infant_weight - 8.1) / (age_in_months - 24) < ((9 - 8.1 ) / (30 - 24)), 'surunderweight',
  85.                                         IF((infant_weight - 9.6) / (age_in_months - 24) < ((10.5 - 9.6) / (30 - 24)), 'underweight', ''))
  86.                                 WHEN
  87.                                     age_in_months > 30
  88.                                         AND age_in_months <= 36
  89.                                 THEN
  90.                                     IF((infant_weight - 9) / (age_in_months - 30) < ((9.8 - 9) / (36 - 30)), 'surunderweight',
  91.                                         IF((infant_weight - 10.5) / (age_in_months - 30) < ((11.4 - 10.5) / (36 - 30)), 'underweight', ''))
  92.                                 WHEN
  93.                                     age_in_months > 36
  94.                                         AND age_in_months <= 42
  95.                                 THEN
  96.                                     IF((infant_weight - 9.8) / (age_in_months - 36) < ((10.3 - 9.8) / (42 - 36)), 'surunderweight',
  97.                                         IF((infant_weight - 11.4) / (age_in_months - 36) < ((12 - 11.4) / (42 - 36)), 'underweight', ''))
  98.                                 WHEN
  99.                                     age_in_months > 42
  100.                                         AND age_in_months <= 48
  101.                                 THEN
  102.                                     IF((infant_weight - 10.3) / (age_in_months - 42) < ((10.9 - 10.3) / (48 - 42)), 'surunderweight',
  103.                                         IF((infant_weight - 12) / (age_in_months - 42) < ((12.8 - 12) / (48 - 42)), 'underweight', ''))
  104.                                 WHEN
  105.                                     age_in_months > 48
  106.                                         AND age_in_months <= 54
  107.                                 THEN
  108.                                     IF((infant_weight - 10.9) / (age_in_months - 48) < ((11.5 - 10.9 ) / (54 - 48)), 'surunderweight',
  109.                                         IF((infant_weight - 12.8) / (age_in_months - 48) < ((13.5 - 12.8) / (54 - 48)), 'underweight', ''))
  110.                                 WHEN
  111.                                     age_in_months > 54
  112.                                         AND age_in_months <= 60
  113.                                 THEN
  114.                                     IF((infant_weight - 11.5) / (age_in_months - 54) < ((12 - 11.5 ) / (60 - 54)), 'surunderweight',
  115.                                         IF((infant_weight - 13.5) / (age_in_months - 54) < ((14.2 - 13.5) / (60 - 54)), 'underweight', ''))
  116.                             END AS nutrition_status,
  117.  
  118. CASE
  119.    WHEN age_in_month_me = 0 THEN IF(weight_me < 2, 'surunderweight', IF(weight_me < 2.4, 'underweight', ''))
  120.    WHEN age_in_month_me > 0 AND age_in_month_me <= 6 THEN
  121.        IF((weight_me - 2) / (age_in_month_me - 0) < ((4.6 - 2) / (6 - 0)), 'surunderweight',
  122.        IF((weight_me - 2.4) / (age_in_month_me - 0) < ((5.5 - 2.4) / (6 - 0)), 'underweight', ''))
  123.            WHEN
  124.                age_in_month_me > 6
  125.                    AND age_in_month_me <= 12
  126.            THEN
  127.                IF((weight_me - 4.6) / (age_in_month_me - 6) < ((6.5- 4.6 ) / (12 - 6)), 'surunderweight',
  128.                    IF((weight_me - 5.5) / (age_in_month_me - 6) < ((7.8 - 5.5) / (12 - 6)), 'underweight', ''))
  129.            WHEN
  130.                age_in_month_me > 12
  131.                    AND age_in_month_me <= 18
  132.            THEN
  133.                IF((weight_me - 6.5) / (age_in_month_me - 12) < ((7.3- 6.5) / (18 - 12)), 'surunderweight',
  134.                    IF((weight_me - 7.8) / (age_in_month_me - 12) < ((8.7 - 7.8) / (18 - 12)), 'underweight', ''))
  135.            WHEN
  136.                age_in_month_me > 18
  137.                    AND age_in_month_me <= 24
  138.            THEN
  139.                IF((weight_me - 7.3) / (age_in_month_me - 18) < ((8.1 - 7.3) / (24 - 18)), 'surunderweight',
  140.                    IF((weight_me - 8.7) / (age_in_month_me - 18) < ((9.6 - 8.7) / (24 - 18)), 'underweight', ''))
  141.            WHEN
  142.                age_in_month_me > 24
  143.                    AND age_in_month_me <= 30
  144.            THEN
  145.                IF((weight_me - 8.1) / (age_in_month_me - 24) < ((9 - 8.1 ) / (30 - 24)), 'surunderweight',
  146.                    IF((weight_me - 9.6) / (age_in_month_me - 24) < ((10.5 - 9.6) / (30 - 24)), 'underweight', ''))
  147.            WHEN
  148.                age_in_month_me > 30
  149.                    AND age_in_month_me <= 36
  150.            THEN
  151.                IF((weight_me - 9) / (age_in_month_me - 30) < ((9.8 - 9) / (36 - 30)), 'surunderweight',
  152.                    IF((weight_me - 10.5) / (age_in_month_me - 30) < ((11.4 - 10.5) / (36 - 30)), 'underweight', ''))
  153.            WHEN
  154.                age_in_month_me > 36
  155.                    AND age_in_month_me <= 42
  156.            THEN
  157.        IF((weight_me - 9.8) / (age_in_month_me - 36) < ((10.3 - 9.8) / (42 - 36)), 'surunderweight',
  158.          IF((weight_me - 11.4) / (age_in_month_me - 36) < ((12 - 11.4) / (42 - 36)), 'underweight', ''))
  159.            WHEN
  160.                age_in_month_me > 42
  161.                    AND age_in_month_me <= 48
  162.            THEN
  163.                IF((weight_me - 10.3) / (age_in_month_me - 42) < ((10.9 - 10.3) / (48 - 42)), 'surunderweight',
  164.                    IF((weight_me - 12) / (age_in_month_me - 42) < ((12.8 - 12) / (48 - 42)), 'underweight', ''))
  165.            WHEN
  166.                age_in_month_me > 48
  167.                    AND age_in_month_me <= 54
  168.            THEN
  169.                IF((weight_me - 10.9) / (age_in_month_me - 48) < ((11.5 - 10.9 ) / (54 - 48)), 'surunderweight',
  170.                    IF((weight_me - 12.8) / (age_in_month_me - 48) < ((13.5 - 12.8) / (54 - 48)), 'underweight', ''))
  171.            WHEN
  172.                age_in_month_me > 54
  173.                    AND age_in_month_me <= 60
  174.            THEN
  175.                IF((weight_me - 11.5) / (age_in_month_me - 54) < ((12 - 11.5 ) / (60 - 54)), 'surunderweight',
  176.                    IF((weight_me - 13.5) / (age_in_month_me - 54) < ((14.2 - 13.5) / (60 - 54)), 'underweight', ''))
  177.        END AS nutrition_status_from_mereenfant
  178.         FROM (
  179.        
  180.         #all surveyed infant
  181. select patient.id,patient.city_code,patient.hospital_code,patient.patient_code ,patient.linked_to_id_patient,
  182. b12345.gender,b12345.dob,b12345.age_in_months,b12345.weight as infant_weight,b12345.date,c12345.*,IF(b12345.gender is not null,IF(b12345.gender=1,"YES","NO"),IF(c12345.gender_me=1,"YES","NO")) as male,
  183. IF(b12345.gender is not null,IF(b12345.gender=2,"YES","NO"),IF(c12345.gender_me=2,"YES","NO")) as female from
  184.         (
  185.             select id_patient from tracking_followup f where f.date>=@start_date and f.date<=@end_date
  186.             union select id_patient from testing_mereenfant  m where m.date>=@start_date and m.date<=@end_date
  187.         ) a12345
  188.         LEFT JOIN
  189.         (
  190.         select id_patient,gender,dob,c.age_in_months,c.weight,date
  191.             from(
  192.                 select i.dob,i.gender,TIMESTAMPDIFF(MONTH,i.dob,f.date) as age_in_months,
  193.                 f.id_patient,f.weight,f.date from tracking_followup f left join tracking_infant i on i.id_patient=f.id_patient
  194.                 where (TIMESTAMPDIFF(MONTH,i.dob,f.date) between 6 and 60) && weight>0
  195.                 and f.date between @start_date and @end_date ) c
  196.         ) b12345 on a12345.id_patient=b12345.id_patient
  197.            
  198.         LEFT JOIN (
  199.             select * from(
  200.             select id_patient,infant_gender as gender_me,infant_weight as weight_me,
  201.             IF(age_in_month_gen<0,age_in_month,age_in_month_gen) as age_in_month_me,date_me from (
  202.                 select id, id_patient ,infant_gender, date as date_me,infant_dob,timestampdiff(month,infant_dob,date) as age_in_month_gen,
  203.                 IF(infant_age_months!=0,infant_age_months,infant_age_weeks*12/52) as age_in_month,infant_age_months ,infant_weight,created_at  from testing_mereenfant
  204.                 ) a123
  205.             ) b123 where (b123.age_in_month_me BETWEEN 6 AND 60) and b123.date_me>=@start_date and b123.date_me<=@end_date and weight_me>0
  206.         ) c12345 on a12345.id_patient=c12345.id_patient
  207.         LEFT JOIN patient on patient.id=a12345.id_patient
  208.         where ((b12345.id_patient is not null) or (c12345.id_patient is not null))
  209.        
  210.        
  211.                 ) a
  212.         WHERE( (a.age_in_months BETWEEN 6 AND 60) or (a.age_in_month_me BETWEEN 6 AND 60)) )b
  213.         where(nutrition_status='underweight' or nutrition_status='surunderweight' or nutrition_status_from_mereenfant='underweight' or nutrition_status_from_mereenfant='surunderweight' ) group by id_patient) malnou ) a
  214. RIGHT JOIN (
  215. select patient.id,patient.city_code,patient.hospital_code,patient.patient_code as patient_surveyed,patient.linked_to_id_patient,
  216. b1234.gender,b1234.dob,c1234.*,IF(b1234.gender is not null,IF(b1234.gender=1,"YES","NO"),IF(c1234.gender_me=1,"YES","NO")) as male,
  217. IF(b1234.gender is not null,IF(b1234.gender=2,"YES","NO"),IF(c1234.gender_me=2,"YES","NO")) as female from
  218.         (
  219.             select id_patient from tracking_followup f where f.date>=@start_date and f.date<=@end_date
  220.             union select id_patient from testing_mereenfant  m where m.date>=@start_date and m.date<=@end_date
  221.         ) a1234
  222.         LEFT JOIN
  223.         (
  224.         select id_patient,gender,dob
  225.             from(
  226.                 select i.dob,i.gender,TIMESTAMPDIFF(MONTH,i.dob,f.date) as age_in_month,
  227.                 f.* from tracking_followup f left join tracking_infant i on i.id_patient=f.id_patient
  228.                 where (TIMESTAMPDIFF(MONTH,i.dob,f.date) between 6 and 60) && weight<>0
  229.                 and f.date between @start_date and @end_date group by f.id_patient) c
  230.         ) b1234 on a1234.id_patient=b1234.id_patient
  231.            
  232.         LEFT JOIN (
  233.             select * from(
  234.             select id_patient,infant_gender as gender_me,infant_weight as weight_me,
  235.             IF(age_in_month_gen<0,age_in_month,age_in_month_gen) as age_in_month_me,date_me from (
  236.                 select id, id_patient ,infant_gender, date as date_me,infant_dob,timestampdiff(month,infant_dob,date) as age_in_month_gen,
  237.                 IF(infant_age_months!=0,infant_age_months,infant_age_weeks*12/52) as age_in_month,infant_age_months ,infant_weight,created_at  from testing_mereenfant
  238.                 ) a123
  239.             ) b123 where (b123.age_in_month_me BETWEEN 6 AND 60) and b123.date_me>=@start_date and b123.date_me<=@end_date and weight_me>0
  240.         ) c1234 on a1234.id_patient=c1234.id_patient
  241.         LEFT JOIN patient on patient.id=a1234.id_patient
  242.         where ((b1234.id_patient is not null) or (c1234.id_patient is not null))  group by a1234.id_patient
  243.                
  244.     ) b on a.patient_code=b.patient_surveyed
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!