SELECT va.pid,
concat(IF(p.prename='002','ด.ญ.',IF(p.prename='004','น.ส.',IF(p.prename='005','นาง',''))),' ',p.fname,' ',p.lname) as personname,
p.idcard,timestampdiff(year,p.birth,va.datecheck) AS age,
p.hnomoi,p.villno AS หมู่ ,
va.pregno,va.pregage,va.datecheck,COUNT(va.pid) AS c_visit,IF(va.pregage<=12,'ก่อน12ฮ๊าฟนับเป็นผลงาน','') AS QOF2 FROM visitanc va
INNER JOIN
(SELECT ps.*,village.villno FROM person ps
INNER JOIN house ON ps.pcucodeperson = house.pcucode AND ps.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode where right(house.villcode,2)!='00') p
ON p.pid=va.pid AND p.pcucodeperson=va.pcucodeperson
WHERE va.ancno=1 AND va.datecheck BETWEEN '2013-04-01' AND '2014-03-31'
GROUP BY va.pid,va.pregno
ORDER BY p.villno,va.pid,va.pregage ASC;