Open Dental Broken Appointments Query

If you are struggling to keep your schedule full and need to identify which patients have a history of missing their visits, this open dental broken appointments query will generate a list of those patients in seconds. By pulling this data, you can proactively reach out to these individuals to re-engage them or discuss your office's broken appointment policy.

The Query

Copy and paste the following code into your User Query window. You can adjust the FromDate and ToDate variables at the top to match the timeframe you wish to review.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-03-31';

SELECT 
    a.AptDateTime AS 'Appointment Date',
    p.LName AS 'Last Name',
    p.FName AS 'First Name',
    p.HmPhone AS 'Home Phone',
    p.WirelessPhone AS 'Cell Phone',
    a.Note AS 'Appointment Note'
FROM appointment a
INNER JOIN patient p ON a.PatNum = p.PatNum
WHERE a.AptStatus = 5
AND DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate
AND p.PatStatus = 0
ORDER BY a.AptDateTime;

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 the text box.

Understanding the Results

• Appointment Date: The original date and time the patient was scheduled to be in the office.
• Last Name / First Name: The patient's name as it appears in the Family Module.
• Home Phone / Cell Phone: Contact numbers pulled directly from the patient's record to help your front desk staff make follow-up calls.
• Appointment Note: Any specific notes attached to that appointment, which may explain why the appointment was broken or provide context regarding the patient's status.

How to Customize

You can easily modify this query to fit your specific reporting needs:

• Change the Date Range: Update the dates inside the single quotes for @FromDate and @ToDate. Ensure you keep the YYYY-MM-DD format.
• Filter by Provider: If you only want to see broken appointments for a specific provider, add this line before the ORDER BY clause:
  AND a.ProvNum = 1 (Replace 1 with the actual ProvNum found in the Provider setup).
• Filter by Clinic: If you operate multiple locations, you can filter by clinic by adding this line:
  AND a.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus only on patients who have broken appointments and currently have no future appointments scheduled, you can add a check to ensure they aren't already on the books.

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.