Analyze No-Show Rates by Day of Week

If you are trying to determine if your office has a recurring issue with broken appointments on specific days, 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 reduce lost production.

The Query

Copy and paste the following code into your User Query window. This query calculates the total number of appointments and the number of broken appointments for each day of the week within your chosen date range.

SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-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 day of the week (e.g., Monday, Tuesday).
• TotalAppointments: The total count of appointments that were scheduled, completed, or broken during the selected date range.
• BrokenAppointments: The count of appointments specifically marked with an AptStatus of "Broken" (5).
• NoShowRatePercent: The percentage of total appointments that resulted in a no-show, calculated as (Broken / Total) * 100.

How to Customize

You can easily adjust this report to fit your specific needs:

• Change the Date Range: Modify the dates in the first two lines. Ensure you keep the format 'YYYY-MM-DD'.
  • Example: SET @FromDate = '2026-01-01';
• Filter by Clinic: If you have multiple locations, add a filter to the WHERE clause.
  • Add this line before the GROUP BY: AND ClinicNum = 1 (Replace 1 with your specific Clinic Number).
• Filter by Provider: To see no-show rates for a specific provider, add this line:
  • AND ProvNum = 1 (Replace 1 with the Provider's internal number).

Variations

Focus on Hygiene Only

If you want to see if your hygiene no-show rate differs from your doctor's schedule, add this line to the WHERE clause:
AND IsHygiene = 1

Exclude Short-Notice Cancellations

If you only want to count "true" no-shows (where the patient didn't call at all) rather than cancellations, you may need to cross-reference the appointment.Note field or check your office's specific usage of the "Broken" status versus other appointment statuses.

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.