set @ds = "2013-10-01";
set @de = "2013-12-31";


SELECT p.pcucodeperson,

SUM(case when p.typelive IN (1,3)AND nc.hbp_d1 IS NOT null AND (year(now())- year(p.birth) BETWEEN 15 AND 34 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร15ht,
SUM(case when p.typelive IN (1,3)AND nc.hbp_d1 IS NOT null AND(year(now())- year(p.birth) BETWEEN 35 AND 59 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร35ht,
SUM(case when p.typelive IN (1,3)AND nc.hbp_d1 IS NOT null AND(year(now())- year(p.birth) BETWEEN 60 AND 120 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร60ht,
SUM(case when p.typelive IN (1,3)AND nc.hbp_d1 IS NOT null AND(year(now())- year(p.birth) BETWEEN 15 AND 120 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากรsumht,
SUM(case when p.typelive IN (1,3)AND nc.bsl IS NOT null AND(year(now())- year(p.birth) BETWEEN 15 AND 34 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร15dm,
SUM(case when p.typelive IN (1,3)AND nc.bsl IS NOT null AND(year(now())- year(p.birth) BETWEEN 35 AND 59 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร35dm,
SUM(case when p.typelive IN (1,3)AND nc.bsl IS NOT null AND(year(now())- year(p.birth) BETWEEN 60 AND 120 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากร60dm,
SUM(case when p.typelive IN (1,3)AND nc.bsl IS NOT null AND(year(now())- year(p.birth) BETWEEN 15 AND 120 AND nc.screen_date  BETWEEN @ds AND @de) THEN 1 END) AS ประชากรsumdm

FROM person p
INNER JOIN ncdpersonscreenall nc ON nc.pid=p.pid AND nc.pcucode=p.pcucodeperson
LEFT JOIN persondeath pd ON p.pid=pd.pid

WHERE   pd.pid IS NULL  AND p.nation ='99' AND p.mumoi <> '0'


GROUP BY p.pcucodeperson ;
