SELECT
person.pid AS HN
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'ชื่อ-สกุล'
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as'วันเกิด'
,YEAR( FROM_DAYS( DATEDIFF( '2012-10-01' ,person.birth ) ) ) as อายุ
,person.idcard AS `เลขบัตรประชาชน`
,house.hno as บ้านเลขที่
,village.villno AS หมู่
,csubdistrict.subdistname as ตำบล
,screen.bsl as 'ระดับน้ำตาล(mg%)'
,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
,left(screen.bmi,5) AS `BMI(kg/m2)`
,screen.waist AS `รอบเอว(ซม.)`
,case when (screen.bsl >='100') and ((screen.hbp_s2 >= '120') or (screen.hbp_d2 >='80'))then 'เสี่ยง DM,เสี่ยงHT'
when screen.bsl >='100'then 'เสี่ยง DM'
when (screen.hbp_s2 >= '120') or (screen.hbp_d2 >='80')then 'เสี่ยง HT'
when screen.bsl <'100' or (screen.bsl is NULL and YEAR( FROM_DAYS( DATEDIFF( '2012-10-01' ,person.birth ) ) ) between 15 and 29 )then 'ปกติ'
when (screen.hbp_s2 or screen.hbp_d2 is null) and (screen.hbp_s1 <'120' and screen.hbp_d1 <'80')then 'ปกติ'
else 'N/A' end  as 'ผลการคัดกรอง'
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 AND RIGHT(house.villcode,2)!='00'
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
INNER 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(1,3)
ORDER BY village.villno;