ผลการคัดกรอง ncd59


เงื่อนไข
1. หากป่วยความดันผลการคัดกรองHT จะว่าง
2. หากป่วยเบาหวานผลการคัดกรอง DM จะว่าง แต่ถ้าคุณบันทึกค่าน้ำตาลในผู้ป่วยเบาหวาน ผลการคัดกรองจะออกมาเสมอ
3. ผลการคัดกรองDM จะออกก็ต่อเมื่อ เลือกวิธีการตรวจ เป็น 3. DTX(อดอาหาร)
4. กลุ่มเป้าหมาย อายุ 35 ปีขึ้นไปทุกคน ยกเว้นกลุ่มป่วยสองโรค


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',
n.screen_date,n.height,n.weight,n.waist,n.hbp_s1,n.hbp_d1,n.bsl,n.bmi,
case when n.bsl <'100' and n.bstest = '3'then 'ปกติ' when n.bsl between '100' and '125' and n.bstest = '3'then 'เสี่ยง' when n.bsl >='126' and n.bstest = '3'then 'สงสัยว่าป่วย' else '' end as 'ผลคัดDM',
case when (n.hbp_s1 < '120' or n.hbp_d1 <'80')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 pcht.chroniccode is not null and pcdm.chroniccode is null) then 'ปกติ'
when ((n.hbp_s1 between '120' and '139') or (n.hbp_d1 between '80' and '89')) 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 pcht.chroniccode is not null and pcdm.chroniccode is null)then 'เสี่ยง'
when ((n.hbp_s1 between '140' and '179') or (n.hbp_d1 between '90' and '109'))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 pcht.chroniccode is not null and pcdm.chroniccode is null) then 'สงสัยว่าป่วย'
when (n.hbp_s1 >= '180' or n.hbp_d1 >='110')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 pcht.chroniccode is not null and pcdm.chroniccode is null) then 'ฉุกเฉิน' else '' end as 'ผลคัดHT'
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
inner join ncd_person_ncd_screen n on person.pid = n.pid
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 n.screen_date between '2015-10-01' and '2016-09-30'
group by person.pid
order by villno, person.pid