select
concat(ctitle.titlename,p.fname,' ',p.lname)as pname,
p.birth as birth,
GetAgeYearNum(p.birth,'2013-10-01') as 'อายุ',
p.idcard as idcard,
p.hnomoi,
SUBSTRING(house.villcode,7,2) as moo
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 '2009-10-01' and '2014-09-30')
and SUBSTRING(house.villcode,7,2) <> '00' and p.typelive in
('1','3') and GetAgeYearNum(p.birth,'2013-10-01') between '30' and '60'
group by p.pid
order by house.villcode
## จากสสอ.เมืองลำปาง
## เปลี่ยนวันที่ตามวันที่ต้องการด้วยนะครับ >> and visitlabcancer.datecheck between '2009-10-01' and '2014-09-30')