สารบัญ

NUTRITION

nutritionf

โครงสร้างแฟ้ม NUTRITION

update bmi_chart แสดงข้อมูลถึง 15 ปี

bmichart

/*WEIGHT น้ำหนัก(กก.) */

set @date1 ='2016-10-01',@date2='2017-09-30',@x = 0;

SELECT @x :=@x+1 as no,person.pname,concat(person.fname,space(2),person.lname)as 'person_name',

person_wbc_nutrition.person_wbc_id,person_wbc_nutrition.nutrition_date,person_wbc_nutrition.body_weight as WEIGHT

from person_wbc_nutrition

INNER JOIN person_wbc on person_wbc.person_wbc_id = person_wbc_nutrition.person_wbc_id

INNER JOIN person on person.person_id = person_wbc.person_id

where person_wbc_nutrition.nutrition_date BETWEEN @date1 and @date2

and person_wbc_nutrition.body_weight is null

ORDER BY  person_wbc_nutrition.person_wbc_id,person_wbc_nutrition.nutrition_date

#---------------------------------

ตัวอย่าง

no pname person_name nutrition_date WEIGHT
1 ด.ญ. ฉันทพิชญา  xxx 9/2/2017 null
2 ด.ญ. ชญาภา  xxx 5/1/2017 null
3 ด.ช. นาราชา  xxx 6/10/2016 null
4 ด.ญ. เกตุขนิฐ  xxx 27/1/2017 null
5 ด.ช. ชญานนท์  xxx 5/1/2017 null

/*HEIGHT ส่วนสูง (ซม.)*/

set @date1 ='2016-10-01',@date2='2017-09-30',@x = 0;

SELECT @x :=@x+1 as no,person.pname,concat(person.fname,space(2),person.lname)as 'person_name',

person_wbc_nutrition.nutrition_date,person_wbc_nutrition.height as height

from person_wbc_nutrition

INNER JOIN person_wbc on person_wbc.person_wbc_id = person_wbc_nutrition.person_wbc_id

INNER JOIN person on person.person_id = person_wbc.person_id

where person_wbc_nutrition.nutrition_date BETWEEN @date1 and @date2

and person_wbc_nutrition.height is null

ORDER BY  person_wbc_nutrition.person_wbc_id,person_wbc_nutrition.nutrition_date

#-------------------------------

ตัวอย่าง

no pname person_name nutrition_date height
1 ด.ญ. ชญาภา  XXX 5/1/2017 null
2 ด.ช. นาราชา  XXX 6/10/2016 null
3 ด.ญ. เกตุขนิฐ  XXX 27/1/2017 null
4 ด.ช. ชญานนท์  XXX 5/1/2017 null
5 ด.ญ. สิทธิดา  XXX 7/10/2016 null

/*HEADCIRCUM เส้นรอบศีรษะ (ซม.) */

set @date1 ='2016-10-01',@date2='2017-09-30',@x = 0,@age = 1;

SELECT @x :=@x+1 as no,person.pname,concat(person.fname,space(2),person.lname)as 'person_name',

timestampdiff(year,person.birthdate,person_wbc_nutrition.nutrition_date)as 'child_age',

person_wbc_nutrition.nutrition_date,person_wbc_nutrition.head_circum_cm

from person_wbc_nutrition

INNER JOIN person_wbc on person_wbc.person_wbc_id = person_wbc_nutrition.person_wbc_id

INNER JOIN person on person.person_id = person_wbc.person_id

where person_wbc_nutrition.nutrition_date BETWEEN @date1 and @date2

and person_wbc_nutrition.head_circum_cm is null

and timestampdiff(year,person.birthdate,person_wbc_nutrition.nutrition_date)=@age

ORDER BY  person_wbc_nutrition.person_wbc_id,person_wbc_nutrition.nutrition_date

#-------------------

ตัวอย่าง

no pname person_name child_age nutrition_date head_circum_cm
1 ด.ญ. กวินทิพย์  XXX 1 20/10/2016 null
2 ด.ช. ปุญญพัฒน์  XXX 1 20/10/2016 null
3 ด.ช. โรจน์ศักดิ์  XXX 1 20/10/2016 null
4 ด.ช. ณัฐพงศ์  XXX 1 20/10/2016 null
5 ด.ญ. กมลนิตย์  XXX 1 20/10/2016 null