select p.pid,
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,
max(case when visitlabcancer.datecheck between '2014-10-01' and '2015-09-30' and visitlabcancer.typecancer= '2'and visitlabcancer.visitno is not null then visitlabcancer.datecheck else '' end) as activity,
max(case when visitlabcancer.datecheck between '2014-10-01' and '2015-09-30' and visitlabcancer.typecancer= '2'and visitlabcancer.visitno is null then visitlabcancer.datecheck else '' end) as coverage
from
person p
inner join ctitle on p.prename = ctitle.titlecode
inner join house on p.pcucodeperson = house.pcucode and p.hcode = house.hcode
left join visitlabcancer on p.pid = visitlabcancer.pid
where p.sex = '2' and CONCAT(p.pid,p.pcucodeperson) NOT IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath)
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;#cr:bee be