SELECT ' ' as ลำดับที่,
p.pid as HN,
p.idcard AS `เลขบัตรประชาชน`,
c.titlename as 'คำนำหน้า',
p.fname AS `ชื่อ`,
p.lname AS `นามสกุล`,
p.birth AS `วันเกิด`,
h.hno as บ้านเลขที่,
v.villno as หมู่ที่,
getAgeYearNum(p.birth,CURDATE()) AS `อายุ`,
max(CASE WHEN v2.visitdate BETWEEN '20131001' AND '20140930' AND v3.codescreen = 'c01' THEN '/' ELSE NULL END) AS 'โรคซึมเศร้า',
max(CASE WHEN v2.visitdate BETWEEN '20131001' AND '20140930' AND v3.codescreen = 'c22' THEN '/' ELSE NULL END) AS 'โรคข้อเข่าเสื่อม',
max(CASE WHEN v2.visitdate BETWEEN '20131001' AND '20140930' AND v3.codescreen = 'c03' THEN '/' ELSE NULL END) AS 'โรคสมองเสื่อม'
FROM
person p
inner join ctitle c on p.prename = c.titlecode
inner join house h on p.hcode = h.hcode and p.pcucodeperson = h.pcucode
inner join village v on h.villcode = v.villcode and h.pcucode = v.pcucode
LEFT JOIN visit v2 ON p.pcucodeperson = v2.pcucodeperson AND p.pid = v2.pid
LEFT JOIN visitscreenspecialdisease v3 ON v2.pcucodeperson = v3.pcucode AND v2.visitno = v3.visitno
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,CURDATE()) > '59'
GROUP BY p.pid
ORDER BY v.villno;