SELECT
CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ - สกุล',
person.idcard AS 'เลขบัตร ปชช.',
concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) AS `วันเกิด`,
getAgeYearNum(person.birth,CURDATE()) AS 'อายุ',
house.hno AS 'บ้านเลขที่',
village.villno AS 'หมู่ที่',
CASE WHEN getAgeYearNum(person.birth,CURDATE()) BETWEEN 24 AND 59 THEN '/' ELSE NULL END AS 'W',
CASE WHEN getAgeYearNum(person.birth,CURDATE()) BETWEEN 6 AND 23 THEN '/' ELSE NULL END AS 'E',
CASE WHEN getAgeYearNum(person.birth,CURDATE()) BETWEEN 0 AND 5 THEN '/' ELSE NULL END AS 'C',
CASE WHEN person.pid IN (SELECT visitanc.pid FROM visitanc WHERE visitanc.datecheck BETWEEN '20131001' AND '20140930') THEN '/' ELSE NULL END AS 'A',
CASE WHEN person.pid IN (SELECT personchronic.pid FROM personchronic) THEN '/' ELSE NULL END AS 'N',
CASE WHEN person.pid IN (SELECT personunable.pid FROM personunable) THEN '/' ELSE NULL END AS 'D',
CASE WHEN getAgeYearNum(person.birth,CURDATE())> 59 THEN '/' ELSE NULL END AS 'O'
FROM
person
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 house.villcode = village.villcode
left join visitancpregnancy a on a.pcucodeperson=person.pcucodeperson and a.pid=person.pid
left join personchronic c on c.pcucodeperson=person.pcucodeperson and c.pid=person.pid
left join personunable u on u.pcucodeperson=person.pcucodeperson and u.pid=person.pid
WHERE
SUBSTRING(house.villcode,7,2)<>'00'
AND person.typelive IN (1,3)
AND person.pid NOT IN (SELECT persondeath.pid FROM persondeath);