SELECT
# จัดกลุ่มอายุ
case when YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) between 15 and 34 then 'กุล่ม15-34' else '>35' end as age_group
,person.pid
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) as personname
,person.idcard
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth
,YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) as อายุ
,house.hno
,village.villno AS หมู่
,village.villname AS บ้าน
# ใช้คำว่า screen.xxxxxx ดึงค่าที่ต้องการให้มาแสดง ตัวอย่างยังไม่หมดนะทำเอง
#เอาค่าผล dtx ค่า bp น้ำหนัก ส่วนสูง รอบเอว.
,screen.bsl
,concat(screen.hbp_s1,'/',screen.hbp_d1) as bp1
,concat(screen.hbp_s2,'/',screen.hbp_d2) as bp2
,screen.weight,screen.height,left(screen.bmi,5) as bmi
,screen.waist
, screen.screen_date as วันscreen
,case when screen.bsl >= '183' then '1'
when screen.bsl BETWEEN '155' and '182' then '2'
when screen.bsl BETWEEN '126' and '154' then '3'
when screen.bsl BETWEEN '100' and '125' then '4'
when screen.bsl <'100' then '5'
when screen.bsl is NULL and YEAR( FROM_DAYS( DATEDIFF( '2014-10-01' ,person.birth ) ) ) between 15 and 29 then '5'
else 'N/A' end as 'colorDM'
,case when screen.hbp_s2 >= '180' or screen.hbp_d2 >= '110' then '1'
when (screen.hbp_s2 BETWEEN '160' and '179') or (screen.hbp_d2 BETWEEN '100' and '109')then '2'
when (screen.hbp_s2 BETWEEN '140' and '159') or 
(screen.hbp_d2 BETWEEN '90' and '99')then '3'
when (screen.hbp_s2 BETWEEN '120' and '139') or (screen.hbp_d2 BETWEEN '80' and '89')then '4'
when screen.hbp_s2 <'120' or screen.hbp_d2 >= '80'then '5'
when (screen.hbp_s2 or screen.hbp_d2 is null) and (screen.hbp_s1 <'120' or screen.hbp_d1 >= '80')then '5'
else 'N/A' end as 'colorHT'
,concat(p1.fname," ",p1.lname)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
LEFT JOIN (select * from ncd_person_ncd_screen
where ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-03-31') screen on person.pid = 
screen.pid
left JOIN person AS p1 ON p1.pid = house.pidvola
WHERE CONCAT(person.pid,person.pcucodeperson) NOT IN (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE deaddate <= CURDATE() or deaddate is not NULL)#ตัดคนตายออกไป
and YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) > 14 #อายุ15ปีขึ้นไป
and person.typelive in (1,3)
ORDER BY อายุ,village.villno;