Select
' ' AS 'ลำดับ',
CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ - สกุล',
person.birth AS 'ว.ด.ป.เกิด',
house.hno AS 'บ้านเลขที่',
village.villno AS 'หมู่ที่',
MAX(CASE WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' THEN visit.weight ELSE NULL END) AS 'น้ำหนัก',
MAX(CASE WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' THEN visit.height ELSE NULL END) AS 'ส่วนสูง',
MAX(CASE WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' AND visit.bmilevel = '1' THEN 'น้ำหนักน้อยกว่าเกณฑ์'
WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' AND visit.bmilevel = '2' THEN 'น้ำหนักค่อนข้างน้อย'
WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' AND visit.bmilevel = '3' THEN 'น้ำหนักตามเกณฑ์'
WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' AND visit.bmilevel = '4' THEN 'น้ำหนักค่อนข้างมาก'
WHEN visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31' AND visit.bmilevel = '5' THEN 'น้ำหนักมากเกินเกณฑ์'
ELSE NULL END) AS 'แปลผล',
MAX(CASE WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' THEN visit.weight ELSE NULL END) AS 'น้ำหนัก',
MAX(CASE WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' THEN visit.height ELSE NULL END) AS 'ส่วนสูง',
MAX(CASE WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' AND visit.bmilevel = '1' THEN 'น้ำหนักน้อยกว่าเกณฑ์'
WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' AND visit.bmilevel = '2' THEN 'น้ำหนักค่อนข้างน้อย'
WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' AND visit.bmilevel = '3' THEN 'น้ำหนักตามเกณฑ์'
WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' AND visit.bmilevel = '4' THEN 'น้ำหนักค่อนข้างมาก'
WHEN visit.visitdate BETWEEN '2014-05-01' AND '2014-06-30' AND visit.bmilevel = '5' THEN 'น้ำหนักมากเกินเกณฑ์'
ELSE NULL END) AS 'แปลผล'
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
left JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
WHERE
person.pid NOT IN (SELECT persondeath.pid FROM persondeath)
AND getAgeYearNum(person.birth,'20140101')BETWEEN '6' AND '14'
AND visit.visitdate BETWEEN '2013-11-01' AND '2014-06-30'
AND RIGHT(village.villcode,2) <> '00'
GROUP BY
person.pid;