SELECT
(SELECT
SUM(CASE WHEN person.typelive in ('1','3') AND GetAgeYearNum(person.birth,'2013-10-01') between '35' and '59' THEN 1 ELSE ''  END)
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND village.villcode = house.villcode
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
and concat(person.pid,person.pcucodeperson) not in (select concat(personchronic.pid, personchronic.pcucodeperson) from  personchronic
where personchronic.chroniccode between 'I10' and 'I15.9' and  personchronic.chroniccode between 'E10' and 'E15')) as 'Pop 15 ปี',
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
     sum(CASE WHEN  (hbp_s1  <'120' and hbp_d1 <'80')OR (hbp_s2  <'120' and hbp_d2 <'80') THEN '1' ELSE null END) AS 'HTขาว',
     sum(CASE WHEN  (hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89') THEN '1' ELSE null END) AS 'HTเขียว',
     sum(CASE WHEN  bsl < '100'  THEN '1' ELSE null END) AS 'DMขาว',
     sum(CASE WHEN  bsl between '100' and '125'  THEN '1'  ELSE null END) AS 'DMเขียว'
FROM
     ncd_person_ncd_screen  INNER JOIN ncd_person  ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
     INNER JOIN person  ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
     age_year between '35' and '59' and ncd_person_ncd_screen.screen_date between '2013-10-01' and '2014-09-30'
     and SUBSTRING(ncd_person.village,7,2) !='00' and person.typelive in ('1','3') and CONCAT(person.pid,person.pcucodeperson) not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath);
