Code รายชื่อกลุ่มเสี่ยงที่ได้รับการปรับพฤติกรรม


select person.idcard,person.pid,
concat(ctitle.titlename,person.fname,'  ',person.lname)as name,
GetAgeYearNum(person.birth,current_date)as age,
house.hno as addr,
right(house.villcode,2) as moo,
max(case WHEN  n.hbp_s1  <'120' and n.hbp_d1 <'80' and n.bsl >= '100' THEN "/" else '' end) as DMRisk,
max(case WHEN  (n.hbp_s1 >= '120' or n.hbp_d1 >= '80')  and  n.bsl < '100' THEN "/" else ''end) as HTRisk,
max(case WHEN  (n.hbp_s1 >= '120' or n.hbp_d1 >= '80') and  n.bsl >= '100' THEN "/" else ''end) as DMHTRisk,
visitdiag.diagcode,visit.visitdate,visit.pressure , v.sugarnumdigit

from person inner join ctitle on person.prename = ctitle.titlecode
inner join ncd_person_ncd_screen n on person.pid = n.pid
inner join visit on person.pid = visit.pid
inner join visitdiag on visit.visitno = visitdiag.visitno
left join visitlabsugarblood v on visit.visitno = v.visitno
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 substring(house.villcode,7,2)<>'00'
and visitdiag.diagcode = 'Z71.3' and visit.visitdate between '2014-10-01' and '2015-09-30'
group by person.pid
order by house.villcode