Query for Long-time Patients
Posted: Wed Aug 13, 2014 2:17 pm
I built a query for finding patients who have been part of our practice for over 25 years. It's limited to ones who have been in for an appointment in the past year and have a future appointment scheduled. I'm finding ways to recognize and thank them for their commitment to our practice. I thought others might find it useful, too. You can set the timeframe you would like at the top.
Code: Select all
SET @FirstVisit='1989-01-01',
@LastProc='2013-09-01',
@NextAppt='2014-08-14';
SELECT p.PatNum,p.BirthDate,p.DateFirstVisit,a.AptDateTime AS 'NextApt'
FROM patient p LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum LEFT JOIN appointment a ON p.PatNum=a.PatNum
WHERE (DATE(p.DateFirstVisit)<@FirstVisit AND DATE(p.DateFirstVisit)>'0001-01-01') AND p.PatStatus=0 AND (DATE(pl.ProcDate)>@LastProc AND pl.ProcStatus=2) AND a.AptStatus IN(1,4)
GROUP BY p.PatNum ORDER BY NextApt;