SELECT
person.pid AS 'Hn',
person.idcard AS 'เลขบัตรปชช',
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
house.hno as 'บ้านเลขที่',
village.villno as 'หมู่ที่',
person.birth as 'ว/ด/ปเกิด',
getAgeYearNum (person.birth,CURRENT_DATE()) as 'ปี',
getAgeMonthNum (person.birth,CURRENT_DATE()) as 'เดือน',
max(visitnutrition.weight) AS 'น้ำหนัก',
max(visitnutrition.tall) AS 'ส่วนสูง',
max(CASE WHEN visitnutrition.nlevel = 1 THEN 'ผอม'
WHEN visitnutrition.nlevel = 2 THEN 'ค่อนข้างผอม'
WHEN visitnutrition.nlevel = 3 THEN 'สมส่วน'
WHEN visitnutrition.nlevel = 4 THEN 'ท้วม'
WHEN visitnutrition.nlevel = 5 THEN 'เริ่มอ้วน'
WHEN visitnutrition.nlevel = 6 THEN 'อ้วน' ELSE null END) AS น้ำหนักตามเกณฑ์ส่วนสูง
FROM person
INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
INNER JOIN visitnutrition ON visit.visitno = visitnutrition.visitno
inner join ctitle on person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND village.villcode = house.villcode
WHERE person.dischargetype = 9
and SUBSTRING(house.villcode,7,2) !='00'
and person.typelive in ('1','3')
AND person.birth BETWEEN '2008-10-01' and '2014-03-31'
AND visit.visitdate BETWEEN '2013-10-01' AND '2013-12-31'
group by person.pid
ORDER BY village.villno;