รายชื่อกลุ่มเป้าหมายคัดกรอง NCD ตามโครงสร้าง 59


เงื่อนไข
1. อายุ 35 ปีขึ้นไป ณวันที่ 1 ตุลาคม 2558
2. เฉพาะ typearea 1+3
3. ตัดกลุ่มป่วยสองโรค



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,'2015-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 '1' else '' end as 'ป่วยht',
case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then '1' 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,'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)
group by person.pid
order by villno, person.pid