select

person.pid as pid,

concat(ctitle.titlename,person.fname,' ',person.lname)as pname,

date_format(person.birth,'%d/%m/%Y') as pbirth,

GetAgeYearNum(person.birth,'2025-10-01')as age,

person.idcard as idcard,

person.hnomoi as hno,

village.villno as villno,

case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then 'Y' else '' end as 'ป่วยht',

case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then 'Y' else '' end as 'ป่วยdm'

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,'2025-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 '2025-10-01' and '2026-09-30')

group by person.pid

order by villno, person.pid