SELECT
house.villcode,
village.villname
,person.pid
,concat(person.fname,' ',person.lname) as personname
,person.idcard
,max(visitlabcancer.datecheck) as datecheck
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth
,YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) as อายุ
,house.hno

FROM person
INNER JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
INNER JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
INNER JOIN visit ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
INNER JOIN visitlabcancer ON visit.pcucode = visitlabcancer.pcucode AND visit.visitno = visitlabcancer.visitno
WHERE person.typelive IN ('1','3')
and sex in ('2')
and visitlabcancer.typecancer = '2'
AND ((person.dischargetype is null) OR (person.dischargetype != '1')) # คัดคนตายออกไป
AND RIGHT(house.villcode,2) != '00'# ไม่ใช่หมู่บ้านนอกเขตบริการ
AND TRIM(person.birth)<>''
AND getAgeYearNum(person.birth,visitlabcancer.datecheck) BETWEEN 30 AND 70
AND visitlabcancer.datecheck BETWEEN '2013-10-01' AND '2014-09-30'
group by person.pid;