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) or (q1.bsl is null and q1.q1_6 =1)) and (q1.bp_s3 >= 120 or q1.bp_d3 >= 80) then 'เสี่ยง DM,เสี่ยง HT'
when (q1.bsl >=100) or (q1.bsl is null and q1.q1_6 =1) then 'เสี่ยง DM' when q1.bp_s3 >= 120 or q1.bp_d3 >= 80 then 'เสี่ยง HT' else null end as 'ผลคัดกรอง'
,vz.visitdate as 'วันที่ปรับเปลี่ยน',case when vz.pressure2 is null then vz.pressure else vz.pressure2 end as 'BP_หลังปรับเปลี่ยน',vz.sugarnumdigit as 'น้ำตาลหลังปรับเปลี่ยน'
,vz2.visitdate as 'วันที่ปรับเปลี่ยน2',case when vz2.pressure2 is null then vz2.pressure else vz2.pressure2 end as 'BP_หลังปรับเปลี่ยน2',vz2.sugarnumdigit as 'น้ำตาลหลังปรับเปลี่ยน2'
,vz3.visitdate as 'วันที่ปรับเปลี่ยน3',case when vz3.pressure2 is null then vz3.pressure else vz3.pressure2 end as 'BP_หลังปรับเปลี่ยน3',vz3.sugarnumdigit as 'น้ำตาลหลังปรับเปลี่ยน3'
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('2012-10-01' ,person.birth))) as Age,person.idcard AS CID,house.hno as Hno,village.villno AS moo
,csubdistrict.subdistname as Tumbon,screen.screen_date 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 screen.screen_q1=0 and screen.screen_q2=0 and screen.screen_q3=0 and screen.screen_q4=0 and screen.screen_q5=0 and screen.screen_q6=0 then 0 ELSE 1 end as q1_6
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 '2014-10-01' and '2015-09-30'and person.typelive in (0,1,3)
AND concat(person.pid,person.pcucodeperson)NOT IN(select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'E1%' and personchronic.chroniccode like'I1%'))q1
left JOIN (SELECT v.pid,v.visitdate,v.pressure,v.pressure2,vb.sugarnumdigit FROM visit v INNER JOIN visitdiag vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno
left JOIN visitlabsugarblood vb ON v.pcucode = vb.pcucode AND v.visitno = vb.visitno
where vd.diagcode ='Z71.3' and v.visitdate ='xxxxxxxx') vz on q1.HN=vz.pid
left JOIN (SELECT v.pid,v.visitdate,v.pressure,v.pressure2,vb.sugarnumdigit FROM visit v INNER JOIN visitdiag vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno
left JOIN visitlabsugarblood vb ON v.pcucode = vb.pcucode AND v.visitno = vb.visitno
where vd.diagcode ='Z71.3' and v.visitdate ='xxxxxxxx') vz2 on q1.HN=vz2.pid
left JOIN (SELECT v.pid,v.visitdate,v.pressure,v.pressure2,vb.sugarnumdigit FROM visit v INNER JOIN visitdiag vd ON v.pcucode = vd.pcucode AND v.visitno = vd.visitno
left JOIN visitlabsugarblood vb ON v.pcucode = vb.pcucode AND v.visitno = vb.visitno
where vd.diagcode ='Z71.3' and v.visitdate ='xxxxxxxx') vz3 on q1.HN=vz3.pid
WHERE ((q1.bsl >=100) or (q1.bsl is null and q1.q1_6 =1))or q1.bp_s3 >= 120 or q1.bp_d3 >= 80
GROUP BY q1.HN order by q1.moo,'ผลคัดกรอง';