Open Dental Unscheduled Patients Query

If you are looking to improve your practice's hygiene retention or fill gaps in your schedule, you need to identify active patients who do not have a future appointment on the books. This open dental unscheduled patients query allows you to quickly generate a list of these individuals so your front desk team can reach out and get them back on the schedule.

The Query

Copy and paste the following code into your User Query window. This query looks for patients with an active status who do not have any appointments scheduled with a status of "Scheduled" (AptStatus 1) in the future.

SET @FromDate = '2026-01-01'; -- Set the start date for your search

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email
FROM patient p
WHERE p.PatStatus = 0
AND p.PatNum NOT IN (
    SELECT a.PatNum 
    FROM appointment a 
    WHERE a.AptStatus = 1 
    AND a.AptDateTime >= @FromDate
)
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, showing you the list of patients who meet your criteria.

Understanding the Results

• PatNum: The unique identification number assigned to 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 email address on file for the patient, useful for digital outreach.

How to Customize

You can easily adjust the focus of this report by modifying the variables at the top of the query:

• Change the Date Range: Modify the @FromDate line. For example, if you want to ensure you aren't missing anyone with an appointment in the next 30 days, set the date to one month from today.
• Filter by Provider: If you only want to see patients assigned to a specific provider, add AND p.PriProv = [ProviderNumber] to the WHERE clause. You can find the Provider Number by going to Setup > Definitions > Providers.

Variations

If you want to narrow this list down to only patients who have insurance, you can add a join to the patplan table:

SELECT p.PatNum, p.LName, p.FName
FROM patient p
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
WHERE p.PatStatus = 0
AND p.PatNum NOT IN (SELECT PatNum FROM appointment WHERE AptStatus = 1 AND AptDateTime >= '2026-01-01')
GROUP BY p.PatNum;

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.