SQL query help on appointments

[brentwood]

Our office has quite a few cancellation for appointments made weeks or months ago. I am planning to send them reminder cards one week before. Could anyone help me with a SQL query that shows appointments that the last completed procedures for those patients were done, say more than 3 weeks ago? Thanks.

[nathansparks]

Sure. I would also encourage you to just have an office policy that you set appoinment status to appointment confirmed if you do not need to confirm it (for instance if an appointment is less than three weeks out, confirm it immediately). Then you can just use the confirmation list ( http://www.open-dent.com/manual/confirmationlist.html ) and send postcards without a query from right inside open dental... way easier than running a query and doing a mail merge.

I could write the query if you call and request it, but if you are writing your own, I can give you some pointers, I will post another reply soon if no-one beats me to it.

[nathansparks]

OK, I just wrote it, easier than trying to make you glue two together

Patients with appointment in date range who had no procedure completed in the given interval prior to the planned appointment
Possible useful to show patients who need an extra reminder in order to show up, not hugely practical (add address2 if you use that field)

SET @pos=0, @StartDate='2008-11-13' , @EndDate='2008-12-13', @DaysSinceLastSeen=21;
SELECT @pos:=@pos+1 AS 'Count', LName,FName, Address, City, State, Zip, ap.AptDateTime,
(SELECT DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y') FROM patient INNER JOIN procedurelog
ON procedurelog.PatNum=patient.PatNum WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum) AS LastVisit
FROM patient p
INNER JOIN appointment ap ON p.PatNum=ap.PatNum AND ap.AptStatus=1
WHERE (DATE(ap.AptDateTime) BETWEEN @StartDate AND @EndDate) AND
((SELECT MAX(ProcDate) FROM patient INNER JOIN procedurelog
ON procedurelog.PatNum=patient.PatNum WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum) < DATE_SUB(ap.AptDateTime, INTERVAL @DaysSinceLastSeen DAY))
ORDER BY aptstatus, p.LName, p.FName ASC;

[brentwood]

Thanks. Nathan. It works great!