SELECT person.pid,concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth AS pbirth,
GetAgeYearNum(person.birth,'2014-10-01')as age,
person.idcard AS pcid,
person.hnomoi AS hno,
person.mumoi AS mu,
ncd_person_ncd_screen.screen_date as 'วันที่คัดกรอง',
ncd_person_ncd_screen.bstest,
ncd_person_ncd_screen.bsl
FROM person INNER JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND village.villcode = house.villcode
left join ncd_person_ncd_screen on person.pid = ncd_person_ncd_screen.pid
WHERE person.typelive IN ('1','3') and GetAgeYearNum(person.birth,'2014-10-01') >= '35'
and SUBSTRING(house.villcode,7,2)<>'00' and  ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30'
and CONCAT(person.pid,person.pcucodeperson)not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE persondeath.deaddate <= CURRENT_DATE)and concat(person.pid,person.pcucodeperson)
IN(select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'E1%' )
group by person.pid
order BY person.pid;