set @START = '2013-10-01';#ใส่วันที่คัดกรองเริ่มตัน
set @end = '2014-02-25';#ใส่วันที่คัดกรองสิ้นสุด
SELECT
right(house.villcode,2) AS `หมู่`,
house.hno,
ncd_person_ncd_screen.pid AS HN,
concat(ctitle.titlename,
person.fname,' ',
person.lname) AS `ชื่อ-สกุล`,
CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS `วันเกิด`,
getAgeYearNum (person.birth,CURDATE()) AS age
,max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end )as HT,
max(case when personchronic.chroniccode BETWEEN 'e10'and 'e14.9' then 1 else null end )as DM,
(case when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end )=1
and max(case when personchronic.chroniccode BETWEEN 'e10'and 'e15.9' then 1 else null end ) THEN 1 else null end )AS 'HT/DM'
,IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2)as systolic
,IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2)as diastolic
,max(case when personchronic.chroniccode BETWEEN 'i11'and 'i15.9' then 1 end )as 'โรคแทรกซ้อนHT'
,case when max(case when personchronic.chroniccode BETWEEN 'i11'and 'i15.9' then 1 end ) = 1 then 'สีดำ'
when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end ) = 1 and
(IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2) >= 180 AND
IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2) >= 110) then 'สีแดง'
when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end ) = 1 and
((IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2) BETWEEN '160' AND'179')or
(IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2) BETWEEN '100'and'109')) then 'สีส้ม'
when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end ) = 1 and
((IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2) BETWEEN '140' AND'159')or
(IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2) BETWEEN '90'and'99')) then 'เหลือง'
when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end ) = 1 and
((IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2) BETWEEN '0' AND'139')or
(IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2) BETWEEN '0'and'89')) then 'สีเขียวเข้ม'
when max(case when personchronic.chroniccode BETWEEN 'i10'and 'i15.9' then 1 else null end ) is null and
((IF(ncd_person_ncd_screen.hbp_s2 is null,ncd_person_ncd_screen.hbp_s1 ,ncd_person_ncd_screen.hbp_s2)>= 120)or
(IF(ncd_person_ncd_screen.hbp_d2 is null,ncd_person_ncd_screen.hbp_d1 ,ncd_person_ncd_screen.hbp_d2) >=80)) then 'สีเขียวเขียวอ่อน'
ELSE 'สีขาว' end as 'แปลสีHT'
,ncd_person_ncd_screen.bsl
,max(case when personchronic.chroniccode BETWEEN 'e10'and 'e10.8' OR
personchronic.chroniccode BETWEEN 'e11'and 'e11.8' OR
personchronic.chroniccode BETWEEN 'e12'and 'e12.8' or
personchronic.chroniccode BETWEEN 'e13'and 'e13.8' or
personchronic.chroniccode BETWEEN 'e14'and 'e14.8'
then 1 else null end )as 'โรคแทรกซ้อนDM'
,case when (max(case when personchronic.chroniccode BETWEEN 'e10'and 'e10.8' OR
personchronic.chroniccode BETWEEN 'e11'and 'e11.8' OR
personchronic.chroniccode BETWEEN 'e12'and 'e12.8' or
personchronic.chroniccode BETWEEN 'e13'and 'e13.8' or
personchronic.chroniccode BETWEEN 'e14'and 'e14.8'
then 1 else null end ))=1 then 'สีดำ'
when max(case when personchronic.chroniccode BETWEEN 'e10'and 'e14.9' then 1 else null end )='1' and bsl >= 183 then 'สีแดง'
when max(case when personchronic.chroniccode BETWEEN 'e10'and 'e14.9' then 1 else null end )='1' and bsl between '155'and'182' then 'สีส้ม'
when max(case when personchronic.chroniccode BETWEEN 'e10'and 'e14.9' then 1 else null end )='1' and bsl BETWEEN '126'and'154' then 'สีเหลือง'
when max(case when personchronic.chroniccode BETWEEN 'e10'and 'e14.9' then 1 else null end )='1' and bsl BETWEEN '0'and'125' then 'สีเขียวเข้ม'
when bsl >= 100 then 'สีเขียวอ่อน'
ELSE 'สีขาว' end as 'แปลสีDM'
from
ncd_person_ncd_screen
INNER JOIN person ON person.pcucodeperson = ncd_person_ncd_screen.pcucode AND person.pid = ncd_person_ncd_screen.pid
INNER JOIN ctitle ON ctitle.titlecode = person.prename
INNER JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN personchronic ON personchronic.pcucodeperson = person.pcucodeperson AND personchronic.pid = person.pid
where
ncd_person_ncd_screen.screen_date BETWEEN @start and @end
and getAgeYearNum (person.birth,CURDATE()) BETWEEN '15'and'65' AND right(house.villcode,2) <> '00'
GROUP BY person.pid
ORDER BY หมู่
ไม่มีความคิดเห็น:
แสดงความคิดเห็น