รายชื่อผู้ป่วยโรค HT DM และระดับ CVD Risk


เงื่อนไข
1. ต้องบันทึกพฤติกรรมสุขภาพ (บุหรี่) ให้ครบ
2. หากต้องการออกข้อมูลเป็นรายเดือน รายงวด รายปี ให้เข้าไปแก้ไข ช่วงเวลาเอง

ขอบคุณ อ. Phuwakorn Sirichaimetha  สำหรับแนวทางการกำหนดโรคเรื้อรังให้แยกเป็นสามกลุ่ม
 


select
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
GetAgeYearNum(person.birth,CURRENT_DATE)as 'อายุ',
concat(person.hnomoi,' ','หมู่',person.mumoi) as 'ที่อยู่',
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,
case when pcdm.chroniccode is not null and pcht.chroniccode is not null then '1' else '' end as htdm,
case when ciga = 0 then 'ไม่สูบ' when ciga in ('1','2')then 'สูบ' else '' end as 'บุหรี่',
left(pressure,locate("/",pressure)-1) as SBP,
case when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=169 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1)  between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and  left(pressure,locate("/",pressure)-1) <= 179 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงมาก'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1)  between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=159 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1)  between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=159 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1)  between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 140 and 159  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูงมาก'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูงมาก'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 140 and 159  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 160 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159  and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179  and personbehavior.ciga = 0  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูงอันตราย'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=139 and personbehavior.ciga in ('1','2') then 'ระดับต่ำ'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when pcdm.chroniccode is not null and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 160 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=179 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 179 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงมาก'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=179 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 179 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 40 and 49 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงมาก'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <=179 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <=179 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 50 and 59 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 139 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') between 60 and 69 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) <= 139 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '1'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'

when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 159 and personbehavior.ciga = 0  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga = 0  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga = 0  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) <= 139 and personbehavior.ciga in ('1','2')  then 'ระดับต่ำ'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 140 and 159 and personbehavior.ciga in ('1','2')  then 'ระดับปานกลาง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) between 160 and 179 and personbehavior.ciga in ('1','2')  then 'ระดับสูง'
when (pcht.chroniccode is not null and pcdm.chroniccode is null ) and getAgeYearNum(birth,'20141001') >= 70 and person.sex = '2'and left(pressure,locate("/",pressure)-1) >= 180 and personbehavior.ciga in ('1','2')  then 'ระดับสูงอันตราย'
else '' end as 'CVD Risk'
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join visit ON person.pcucodeperson = visit.pcucodeperson and person.pid = visit.pid
left join visitdiag on visit.visitno = visitdiag.visitno
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
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
left join personbehavior on person.pid = personbehavior.pid and person.pcucodeperson = personbehavior.pcucodeperson
where visit.visitdate between '2014-10-01' and '2015-09-30'
and SUBSTRING(house.villcode,7,2)<> '00' and (visitdiag.diagcode between 'E10' and 'E15.9' or (visitdiag.diagcode ='I10'))
and pc.cup = pc.pcucodeperson
group by pc.pid
order by village.villno