SELECT p.pid HN,p.fname ชื่อ,p.lname สกุล,p.birth วันเดือนปีเกิด,getAgeYearNum(p.birth,curdate()) อายุปี,h.hno บ้านเลขที่,right(h.villcode,2) หมู่
FROM person p INNER JOIN house h
ON p.pcucodeperson=h.pcucode AND p.hcode=h.hcode
LEFT JOIN (SELECT pc.pcucodeperson,pc.pid FROM personchronic pc WHERE pc.chroniccode BETWEEN 'e10'and 'e14.9') chronic
ON p.pcucodeperson=chronic.pcucodeperson and p.pid=chronic.pid
WHERE LEFT(p.birth,4)<='1998' and p.typelive in ('1','3') AND p.dischargetype<>'1' AND right(h.villcode,2)>'00' AND chronic.pid is null AND CONCAT(p.pcucodeperson,p.pid) NOT IN (SELECT CONCAT(ncd.pcucode,ncd.pid)FROM ncdpersonscreenall ncd WHERE ncd.screen_date >='2012-10-01')
GROUP BY p.pid