SELECT
p.pid 'Hn',
concat(c.titlename,p.fname,' ',p.lname) AS `ชื่อ - สกุล`,
p.idcard เลขบัตรประชาชน,
h.hno AS `บ้านเลขที่`,
v.villno AS `หมู่ที่`,
getAgeYearNum (p.birth,20141001) อายุ,
CASE WHEN v2.vaccinecode = 'dTC' THEN v2.dateepi ELSE NULL END วันที่รับบริการ,
max(CASE WHEN v2.vaccinecode = 'dTC' AND v3.diagcode = 'z23.5' THEN '/' ELSE NULL END) บาดทะยัก,
max(CASE WHEN v2.vaccinecode = 'dTC' AND v3.diagcode = 'z23.6' THEN '/' ELSE NULL END) คอตีบ,
p.telephoneperson เบอร์โทร,
p1.osm as 'อสม'
FROM
person AS p
INNER JOIN house AS h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village AS v ON h.pcucode = v.pcucode AND h.villcode = v.villcode
INNER JOIN ctitle AS c ON p.prename = c.titlecode
LEFT JOIN visitepi AS v2 ON p.pcucodeperson = v2.pcucodeperson AND p.pid = v2.pid
LEFT JOIN visitdiag AS v3 ON v2.pcucodeperson = v3.pcucode AND v2.visitno = v3.visitno
LEFT JOIN(select p1.pid,concat(p1.fname,' ',p1.lname)as osm from person p1)p1 on h.pidvola=p1.pid
WHERE
right(v.villcode,2) <> '00' AND
p.pid NOT IN ((select persondeath.pid from persondeath)) AND
p.typelive IN ('1', '3') AND
p.dischargetype = '9' AND
getAgeYearNum (p.birth,20141001) BETWEEN '20' AND '50'
GROUP BY
p.pid
ORDER BY
v.villno;