Open Dental No-Show Rate Query

If you are trying to determine which days of the week your practice experiences the most broken appointments, this open dental no show rate query will provide the data you need. By analyzing your appointment history, you can identify trends and adjust your scheduling strategy to minimize lost production.

The Query

This query calculates the total number of appointments and the number of "Broken" appointments (AptStatus = 5) for each day of the week within your specified date range.

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

SELECT 
    DAYNAME(AptDateTime) AS 'DayOfWeek',
    COUNT(*) AS 'TotalAppointments',
    SUM(CASE WHEN AptStatus = 5 THEN 1 ELSE 0 END) AS 'BrokenAppointments',
    ROUND(SUM(CASE WHEN AptStatus = 5 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS 'NoShowRatePercent'
FROM appointment
WHERE AptDateTime >= @FromDate 
AND AptDateTime <= @ToDate
AND AptStatus IN (1, 2, 5) -- 1=Scheduled, 2=Complete, 5=Broken
GROUP BY DAYOFWEEK(AptDateTime)
ORDER BY DAYOFWEEK(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.

Understanding the Results

• DayOfWeek: The name of the day (e.g., Monday, Tuesday).
• TotalAppointments: The total count of appointments that were either scheduled, completed, or broken during the selected timeframe.
• BrokenAppointments: The number of appointments marked specifically as "Broken" (AptStatus 5) in the Appointments Module.
• NoShowRatePercent: The calculated percentage of broken appointments relative to the total appointments for that specific day of the week.

How to Customize

You can easily adjust the parameters of this report to fit your needs:

• Change the Date Range: Modify the dates in the first two lines of the query. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Provider: If you want to see the no-show rate for a specific provider, add AND ProvNum = X (replace X with the provider's number) to the WHERE clause.
• Filter by Clinic: If you have multiple locations, add AND ClinicNum = Y (replace Y with the clinic number) to the WHERE clause.

Variations

If you want to focus only on hygiene appointments, you can add a filter to the WHERE clause:

• Hygiene Only: Add AND IsHygiene = 1 to the WHERE clause. This will exclude restorative appointments and show you if your hygiene department has a different no-show trend than your doctor's schedule.

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.