

SELECT person.pid,person.fname,person.lname,person.idcard

,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date,

getAgeYearNum(person.birth,CURDATE()) AS `อายุ`,concat(person.hnomoi,' ','ม.',person.mumoi)as 'ที่อยู่่'

,group_concat(pc.chroniccode) as กลุ่มเป้าหมาย

FROM person

inner join (select pc.pid,pc.chroniccode,pc.cup from personchronic pc WHERE (pc.chroniccode like 'E1%' or pc.chroniccode like 'I25%' or pc.chroniccode like 'C%'

or pc.chroniccode like 'J44%' or pc.chroniccode like 'J45%' or pc.chroniccode like 'N18%')) pc on pc.pid=person.pid

where person.dischargetype=9 and person.typelive in (0,1,3) and pc.cup=person.pcucodeperson

GROUP BY person.pid

union

SELECT person.pid,person.fname,person.lname,person.idcard

,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date,

getAgeYearNum(person.birth,CURDATE()) AS `อายุ`,concat(person.hnomoi,' ','ม.',person.mumoi)as 'ที่อยู่่','ผู้สูงอายุ 65+' as กลุ่มเป้าหมาย

FROM person

where getAgeYearNum(person.birth,'2013-10-01') >=65 and person.dischargetype=9 and person.typelive in (0,1,3)

AND concat(person.pid,person.pcucodeperson)not in(select concat(pc.pid,pc.pcucodeperson)from personchronic pc

WHERE (pc.chroniccode like 'E1%' or pc.chroniccode like 'I25%' or pc.chroniccode like 'C%' or pc.chroniccode like 'J44%' or pc.chroniccode like 'J45%' or pc.chroniccode like 'N18%'))

GROUP BY person.pid

UNION

SELECT person.pid,person.fname,person.lname,person.idcard

,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date

,getAgeYearNum(person.birth,CURDATE()) AS `อายุ`,concat(person.hnomoi,' ','ม.',person.mumoi)as 'ที่อยู่่','เด็ก 6 เดือน -2ปี' as กลุ่มเป้าหมาย

FROM person where getAgemonth(person.birth,'2013-10-01') between 6 and 24 and person.dischargetype=9 and person.typelive in (0,1,3)

