Operatory Utilization by Day of Week

If you are trying to determine which days of the week your operatories are most heavily booked, you need a clear view of your appointment volume. This open dental operatory utilization query helps you identify trends in your schedule so you can make informed decisions about staffing and block scheduling.

The Query

You can copy and paste the following code directly into your User Query window. This query calculates the number of scheduled appointments per operatory for each day of the week within your chosen date range.

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

SELECT 
    o.OpName AS 'Operatory',
    DAYNAME(a.AptDateTime) AS 'DayOfWeek',
    COUNT(a.AptNum) AS 'AppointmentCount'
FROM appointment a
INNER JOIN operatory o ON a.Op = o.OperatoryNum
WHERE a.AptDateTime >= @FromDate 
  AND a.AptDateTime <= @ToDate
  AND a.AptStatus IN (1, 2) -- Scheduled or Complete
  AND o.IsHidden = 0
GROUP BY o.OpName, DAYNAME(a.AptDateTime), DAYOFWEEK(a.AptDateTime)
ORDER BY o.OpName, DAYOFWEEK(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

• Operatory: The name of the operatory as defined in your Setup > Appointments > Operatories list.
• DayOfWeek: The day of the week (e.g., Monday, Tuesday) the appointments occurred.
• AppointmentCount: The total number of appointments scheduled or completed in that specific operatory on that specific day of the week during your selected date range.

How to Customize

You can easily adjust this report to fit your specific needs by modifying the first two lines of the query:

• 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 Clinic: If you have multiple locations and only want to see one, add a line to the WHERE clause: AND a.ClinicNum = 1. (Replace 1 with your specific ClinicNum, which you can find in the Setup > Clinics list).

Variations

If you want to focus specifically on hygiene utilization, you can add a filter to the WHERE clause to only count appointments marked as hygiene:

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

If you want to exclude broken appointments or other statuses, the a.AptStatus IN (1, 2) line is already handling that by only including scheduled (1) and completed (2) appointments. You can add other status numbers from the Open Dental manual if you wish to include them.

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.