Active Patients with No Upcoming Appointment

If you are looking to increase your production and fill gaps in your schedule, you need to identify active patients who have fallen through the cracks. This open dental unscheduled patients query provides a clean list of patients currently marked as "Active" who do not have any future appointments scheduled, allowing your front desk team to reach out and get them back on the books.

The Query

Copy and paste the following code into your User Query window. This query looks for patients with a status of "Patient" (Active) and excludes anyone who has an appointment scheduled for today or any date in the future.

/* Set the date to today to ensure we only look for future appointments */
SET @Today = CURDATE();

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email,
    MAX(a.AptDateTime) AS LastSeen
FROM patient p
LEFT JOIN appointment a ON p.PatNum = a.PatNum 
    AND a.AptStatus IN (1, 4) /* Scheduled or ASAP */
    AND a.AptDateTime >= @Today
WHERE p.PatStatus = 0 /* Active Patients */
GROUP BY p.PatNum
HAVING COUNT(a.AptNum) = 0
ORDER BY p.LName, p.FName;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below, which you can then export to Excel if needed.

Understanding the Results

• PatNum: The unique identification number for the patient in your database.
• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: Contact numbers to help your team reach out for scheduling.
• Email: The patient's email address for digital outreach.
• LastSeen: This shows the date of the patient's most recent completed appointment, helping you prioritize who to call first (e.g., someone seen 6 months ago vs. 3 years ago).

How to Customize

You can easily narrow down this list to make it more manageable for your team.

• Filter by Provider: If you only want to see patients assigned to a specific provider, add this line right before the GROUP BY clause:
  AND p.PriProv = 1 (Replace 1 with the actual ProvNum of the provider).
• Filter by Clinic: If you are a multi-location practice, add this line before the GROUP BY clause:
  AND p.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus specifically on hygiene patients who are overdue, you can add a join to the recall table to ensure you are only calling patients who are actually due for a cleaning.

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.