SELECT person.pid
,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS 'เลขบัตรประชาชน'
,CONCAT(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'ชื่อ-นามสกุล'
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS 'วันเดือนปีเกิด'
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS 'อายุ'
,person.hnomoi AS 'บ้านเลขที่'
,SUBSTRING(house.villcode,7,2) AS 'หมู่ที่'
,GROUP_CONCAT(visitdiag.diagcode) AS 'วินิจฉัย'
,visit.visitdate AS 'วันที่คัดกรอง'
,visit.username AS 'คนที่คีย์คัดกรอง'
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN visit ON person.pid=visit.pid AND person.pcucodeperson=visit.pcucodeperson
LEFT JOIN visitdiag ON visit.visitno=visitdiag.visitno AND visit.pcucodeperson=visitdiag.pcucode
WHERE persondeath.pid IS  NULL
and person.pid NOT IN (select visit.pid FROM visit 
INNER JOIN visitdiag ON visit.visitno= visitdiag.visitno where visit.visitdate between '2015-10-01' and '2016-09-30' AND visitdiag.diagcode='Z01.0' OR visitdiag.diagcode LIKE'H545' )
AND getAgeYearNum (birth,CURDATE()) >= 60
#AND visitdiag.diagcode IN ('Z01.0','H54%')
AND visit.visitdate BETWEEN '2015-10-01' AND '2016-09-30'
AND person.typelive in ('0','1','3')
AND right((house.villcode),2) != 00
GROUP BY pid
ORDER BY visitdate;