select q1.HN,q1.name as ชื่อสกุล,q1.BD as 'วันเกิด',q1.Age as อายุ,q1.CID as เลขบัตรประชาชน,q1.Hno as บ้านเลขที่,q1.moo as หมู่,q1.Tumbon as ตำบล,q1.sd as 'วันที่ คัดกรอง',q1.bsl as 'ระดับน้ำตาล(mg%)'
,concat(q1.bp_s3,'/',q1.bp_d3)as 'BP',q1.BMI as 'BMI(kg/m2)',q1.waist as 'รอบเอว(ซม.)'
,case when (q1.bsl >=100) and (q1.bp_s3 >= 120 or q1.bp_d3 >= 80) and (q1.HT_type=0 and q1.DM_type=0) then 'เสี่ยง DM,เสี่ยง HT'
when q1.bsl >=100 and (q1.DM_type=0)then 'เสี่ยง DM'
when (q1.bp_s3 >= 120 or q1.bp_d3 >= 80) and (q1.HT_type=0)then 'เสี่ยง HT'
else null end as 'ผลคัดกรอง'
from(
SELECT
person.pid AS HN
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'name'
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as BD
,YEAR(FROM_DAYS(DATEDIFF('2013-10-01' ,person.birth))) as Age
,person.idcard AS CID
,house.hno as Hno
,village.villno AS moo
,csubdistrict.subdistname as Tumbon
,concat(substr(screen.screen_date,9,2),'/',substr(screen.screen_date,6,2),'/',substr(screen.screen_date,1,4)+543)as sd
,screen.bsl as bsl
,concat(screen.hbp_s1,'/',screen.hbp_d1) as BP1
,case when screen.hbp_s2 is not null then concat(screen.hbp_s2,'/',screen.hbp_d2) else '-' end as BP2
,case when screen.hbp_s2 is not null then screen.hbp_s2 when screen.hbp_s2 is null then screen.hbp_s1 else 'N/A' end as bp_s3
,case when screen.hbp_d2 is not null then screen.hbp_d2 when screen.hbp_d2 is null then screen.hbp_d1 else 'N/A' end as bp_d3
,left(screen.bmi,5) AS BMI
,screen.waist AS waist
,case when person.pid in (select pc.pid from personchronic pc where chroniccode between 'I10' and 'I15.9') then 1 else 0 end as HT_type
,case when person.pid in (select pc.pid from personchronic pc where chroniccode between 'E10' and 'E14.9') then 1 else 0 end as DM_type
FROM person
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN ncd_person_ncd_screen screen ON person.pcucodeperson = screen.pcucode AND person.pid = screen.pid
left JOIN csubdistrict ON person.provcodemoi = csubdistrict.provcode AND person.distcodemoi = csubdistrict.distcode AND person.subdistcodemoi = csubdistrict.subdistcode
WHERE screen.screen_date between '2013-10-01' and '2014-09-30'
and person.typelive in (0,1,3))q1
WHERE (q1.bsl >=100) or (q1.bp_s3 >= 120 or q1.bp_d3 >= 80)
AND q1.HN NOT IN(select q1.pid from(select pc.pid,group_concat(distinct case when pc.chroniccode like 'E1%' then 'DM' when pc.chroniccode like 'I1%' then 'HT' else '' end) as 'Disease'
from personchronic pc where pc.chroniccode between 'E10' and 'E14.9' or pc.chroniccode between 'I10' and 'I15.9'group by pid)q1 where q1.disease in ('DM,HT','HT,DM'))
GROUP BY q1.HN order by q1.moo,q1.Hno*1;