SELECT person.pid
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as 'Name'
,person.idcard
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date
,getagemonth(person.birth,'2013-10-31') AS Age_M
,house.hno as บ้านเลขที่
,village.villno AS หมู่
,village.villname AS บ้าน
,person.typelive as TypeArea
FROM person
INNER JOIN house ON person.hcode=house.hcode and person.pcucodeperson=house.pcucode
INNER JOIN village ON house.villcode=village.villcode and house.pcucode = village.pcucode
left join ctitle on person.prename = ctitle.titlecode
WHERE getagemonth(person.birth,'2013-10-31') <= 71
and person.typelive in (1,3)
#AND right(villcode,2)!= '00' #หมู่บ้านนอกเขตบริการออก
and CONCAT(person.pid,person.pcucodeperson) NOT IN (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE deaddate <= CURDATE() or deaddate is not NULL)#ตัดคนตายออกไป
AND CONCAT(person.pid,person.pcucodeperson)NOT IN ( SELECT CONCAT(person.pid,person.pcucodeperson)FROM visit
INNER JOIN visitnutrition ON visit.pcucode = visitnutrition.pcucode AND visit.visitno = visitnutrition.visitno
INNER JOIN person ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
WHERE visit.visitdate BETWEEN '2013-10-01' AND '2013-10-31')
ORDER BY village.villno;