รายชื่อคัดกรอง และแปลผลความเสี่ยง





select
person.pid as pid,
concat(ctitle.titlename,person.fname,'    ',person.lname)as pname,
age_year,
person.idcard as pcid,
person.sex as sex,
person.hnomoi as hno,
person.mumoi as mu,
hbp_s1,hbp_d1,bsl,
DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date,
max(case when hbp_s1  <'120' and hbp_d1 <'80' and bsl < '100' then "/" else '' end) as "ปกติ",
max(case when hbp_s1  <'120' and hbp_d1 <'80' and bsl >= '100' then "/" else '' end) as "เสี่ยงDM",
max(case when (hbp_s1 >= '120' or hbp_d1 >= '80')  and  bsl < '100' then "/"  else '' end) as "เสี่ยงHT",
max(case when (hbp_s1 >= '120' or hbp_d1 >= '80') and  bsl >= '100' then "/"  else '' end) as "เสี่ยงDM&HT"
from     ncd_person_ncd_screen inner join person on ncd_person_ncd_screen.pid = person.pid
     inner join ctitle on person.prename = ctitle.titlecode
     inner join ncd_person  on ncd_person_ncd_screen.pcucode = ncd_person.pcucode and ncd_person_ncd_screen.pid = ncd_person.pid
where
     age_year >= '35' and ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30' and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode) not in
     (select concat(ncd_person_ncd_hist.pid, ncd_person_ncd_hist.pcucode)
      from  ncd_person_ncd_hist) and SUBSTRING(ncd_person.village,7,2) <>'00'
group by person.pid
order by ncd_person.village