

select case when q1.Age between 15 and 34 then '15-34 ปี'
when q1.Age between 35 and 59 then '35-59 ปี'
when q1.Age >= 60 then '>= 60 ปี' else 'ต่ำกว่า 15 ปี' end as 'Age_group'
 ,count(DISTINCT q1.HN) as 'คัดกรอง HT'
 ,count(DISTINCT case when q1.bp_s3 < 120 and q1.bp_d3 < 80 then q1.HN else null end) as 'BP < 120/80'
 ,count(distinct case when (q1.bp_s3 between 120 and 139) and (q1.bp_d3 between 80 and 89) then q1.HN 
 when (q1.bp_s3 between 120 and 139) and (q1.bp_d3 <80)then q1.HN
 when (q1.bp_s3 < 120) and (q1.bp_d3 between 80 and 89)then q1.HN else null end) as 'BP 120-139/80-89'
 ,count(distinct case when q1.bp_s3 >= 140 and q1.bp_d3 >=90 then q1.HN 
 when (q1.bp_s3 >= 140) and (q1.bp_d3 <90)then q1.HN
 when (q1.bp_s3 < 140) and (q1.bp_d3 >= 90)then q1.HN else null end) as 'BP >= 140/90'
 from
 (SELECT
 person.pid AS HN
 ,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'name'
 ,YEAR( FROM_DAYS( DATEDIFF( '2013-10-01' ,person.birth ) ) ) as Age
 ,house.hno as Hno
 ,village.villno AS moo
 ,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
 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
 WHERE screen.screen_date between '2014-10-01' and '2015-09-30'
 AND concat(person.pid,person.pcucodeperson)NOT IN(select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'I1%')) q1
 GROUP BY Age_group;
