select
concat(ctitle.titlename,p.fname,' ',p.lname)as pname,
p.birth as birth,
GetAgeYearNum(p.birth,'2014-10-01') as 'อายุ',
p.idcard as idcard,
p.hnomoi,
SUBSTRING(house.villcode,7,2) as moo,
p.typelive
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
where p.sex = '2' and CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
and CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(visitlabcancer.pid,visitlabcancer.pcucodeperson)from visitlabcancer where visitlabcancer.typecancer= '2'
and visitlabcancer.datecheck between '2014-10-01' and '2018-09-30')
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in ('1','3') and GetAgeYearNum(p.birth,'2014-10-01') between '30' and '60'
group by p.pid
order by house.villcode;