Open Dental Broken Appointments Query

If you need to identify which patients have missed their scheduled visits to improve your office's attendance rates, this open dental broken appointments query pulls that list in seconds. It allows you to see exactly who failed to show up, when it happened, and which provider they were scheduled to see.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET variables at the top to change the date range for your report.

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

SELECT 
    p.LName, 
    p.FName, 
    a.AptDateTime, 
    a.Note AS AppointmentNote,
    prov.Abbr AS Provider
FROM appointment a
INNER JOIN patient p ON a.PatNum = p.PatNum
LEFT JOIN provider prov ON a.ProvNum = prov.ProvNum
WHERE a.AptStatus = 5
AND a.AptDateTime >= @FromDate
AND a.AptDateTime <= @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. Change the dates in the first two lines (@FromDate and @ToDate) to your desired range.
5. Click Submit Query.
6. The results will display in the grid below.

Understanding the Results

• LName/FName: The patient's last and first name.
• AptDateTime: The original date and time the appointment was scheduled.
• AppointmentNote: Any notes attached to that specific appointment (often where staff record why the patient broke the appointment).
• Provider: The provider assigned to that appointment.

How to Customize

You can easily filter this report to meet specific needs by modifying the WHERE clause:

• Filter by Provider: To see broken appointments for a specific provider only, add this line before the ORDER BY clause:
  AND prov.Abbr = 'DDS1' (Replace 'DDS1' with the actual provider abbreviation).
• Filter by Clinic: If you have multiple locations, add this line:
  AND a.ClinicNum = 1 (Replace '1' with your specific ClinicNum).
• Change Date Range: Simply edit the 'YYYY-MM-DD' format in the SET lines at the top of the query.

Variations

If you want to see only broken appointments for hygiene specifically, you can add a filter for the IsHygiene column:

-- Add this to the WHERE clause
AND a.IsHygiene = 1

If you want to see broken appointments for patients who are currently marked as "Inactive" (perhaps to reach out and re-activate them), change p.PatStatus = 0 to p.PatStatus = 2.

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.