select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-นามสกุล',
CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS 'วันเดือนปีเกิด',
GetAgeYearNum(person.birth,'2015-10-01')as 'อายุ',
person.idcard as 'เลขบัตรประชาชน',
person.hnomoi as 'หมู่ที่',
village.villno as 'หมู่ที่',
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then 'ความดัน' else '' end as 'ผู้ป่วยความดัน',
case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then 'เบาหวาน' else '' end as 'ผู้ป่วยเบาหวาน'
from person inner join ctitle on person.prename = ctitle.titlecode
left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
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
where person.typelive in ('1','3') and person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2015-10-01')>= '35' and substring(house.villcode,7,2)<>'00'
and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcdm.chroniccode is not null and pcht.chroniccode is not null)
and person.pid not in (select pid from ncd_person_ncd_screen where screen_date between '2015-10-01' and '2016-09-30')
group by person.pid
order by villno, person.pid;