Retrieving New Patients Who Completed Procedures but without creation of an Appointment

[beacondental]

Hi Team,

How can I retrieve a list of new patients who visited the office for the first time, completed at least one procedure, but left without having any appointments created? Which tables or APIs should I check ?

Thank You

[justine]

Hi Team,

How can I retrieve a list of new patients who visited the office for the first time, completed at least one procedure, but left without having any appointments created? Which tables or APIs should I check ?

Thank You

Hello beacondental,

I believe Query Example 1215 may work for you.

Thanks!

[ishubham2101]

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.