SELECT
visit.pid,
persondeath.deaddate,
max(visit.visitdate)
FROM
visit
INNER JOIN persondeath ON visit.pid = persondeath.pid AND persondeath.pcucodeperson=visit.pcucodeperson
where visit.visitdate > persondeath.deaddate
group by pid;