SELECT
case when YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) between 15 and 34 then 'กุล่ม15-34'
when YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) between 35 and 59 then 'กุล่ม35-59'
else '>59' 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.bsl
,concat(screen.hbp_s1) as bp11
,concat(screen.hbp_d1) as bp22
,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 <= '100' then 'ขาวปกติ'
when screen.bsl BETWEEN '100' AND '125' then 'เขียวอ่อนเสี่ยง'
when screen.bsl > '125' then 'ป่วย'
when screen.bsl is null then 'ยังไม่ได้คัดกรอง'
when screen.bsl is NULL and YEAR( FROM_DAYS( DATEDIFF( '2013-01-01',person.birth ) ) ) between 15 and 29 then '5'
else 'N/A' end as 'colorDM'
,case when screen.hbp_s1 <= '120' or screen.hbp_d1 <= '80'then 'ขาวปกติ'
when (screen.hbp_s1 BETWEEN '120' and '139') or (screen.hbp_d1 BETWEEN '80' and '89')then 'เขียวอ่อนเสี่ยง'
when screen.hbp_s1 >'139' or screen.hbp_d1 > '89'then 'ป่วย'

when (screen.hbp_s1 or screen.hbp_d2 is null) and (screen.hbp_s2 <'120' or screen.hbp_d1 >= '80')then 'ยังไม่ได้คัดกรอง'
else 'ยังไม่ได้คัดกรอง' 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) not in ('00','99')
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-09-30') screen on person.pid = screen.pid
left JOIN person AS p1 ON p1.pid = house.pidvola
INNER JOIN ncd_person_ncd ON person.pcucodeperson = ncd_person_ncd.pcucode AND person.pid = ncd_person_ncd.pid
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
and person.typelive in (1,3)
and person.nation in('99')
and chronic_flag='0'
ORDER BY village.villno,อายุ;