SELECT v.pid,concat(ctitle.titlename,person.fname," ",person.lname) as 'ชื่อ-สกุล'
,MAX(v.visitdate) as 'วันที่มารับบริการครั้งสุดท้าย'
,p.deaddate as 'วันตาย'
FROM visit v
INNER JOIN person ON v.pid = person.pid AND v.pcucodeperson = person.pcucodeperson
LEFT JOIN persondeath p ON v.pid = p.pid AND v.pcucodeperson =p.pcucodeperson
left JOIN ctitle on person.prename=ctitle.titlecode
WHERE p.deaddate <= v.visitdate AND v.visitdate BETWEEN '2013-10-01' AND '2014-09-30'
GROUP BY v.pid
ORDER BY v.pid;