The tables you need are patient, procedurelog and appointment. patient.DateFirstVisit gives you "new", procedurelog with ProcStatus = 2 gives you "completed at least one procedure", and the absence of a future appointment row is the "left without anything booked" part.
Code: Select all
SELECT
pat.PatNum,
pat.LName,
pat.FName,
pat.WirelessPhone,
pat.Email,
pat.DateFirstVisit,
pat.ClinicNum,
COUNT(pl.ProcNum) AS completed_procs,
ROUND(SUM(pl.ProcFee), 2) AS completed_dollars,
MAX(pl.ProcDate) AS last_completed
FROM patient pat
INNER JOIN procedurelog pl
ON pl.PatNum = pat.PatNum
AND pl.ProcStatus = 2
WHERE pat.PatStatus = 0
AND pat.DateFirstVisit >= CURDATE() - INTERVAL 180 DAY
AND NOT EXISTS (
SELECT 1
FROM appointment ap
WHERE ap.PatNum = pat.PatNum
AND ap.AptStatus IN (1, 4)
AND ap.AptDateTime > NOW()
)
GROUP BY pat.PatNum, pat.LName, pat.FName, pat.WirelessPhone, pat.Email,
pat.DateFirstVisit, pat.ClinicNum
ORDER BY completed_dollars DESC;
The 180 is your definition of "new" - change it to whatever window you actually mean. It sorts by what they already spent with you, on the theory that the ones who spent the most and walked out unbooked are the ones worth calling first.
One assumption worth checking: "no appointment" here means no future appointment with status Scheduled or ASAP. A patient sitting on the unscheduled list still appears in this report, because they aren't actually booked. If you'd rather exclude them, add AptStatus 3 to that NOT EXISTS.
ProcStatus 6 is deleted, so it never gets counted here - worth knowing generally, since including it quietly inflates any production query.
Run it read-only first. Seven other Open Dental report queries here, free:
https://github.com/ishubham21/opendental-queries - each one is run against Open Dental's published schema before it goes up.