Provider Schedule Utilization Query

If you are trying to determine how effectively your providers are using their time, you need a clear view of their scheduled hours compared to their total available time. This open dental schedule utilization query provides a breakdown of scheduled appointments by provider, allowing you to identify gaps in your schedule and measure overall chair-side productivity.

The Query

This query calculates the total number of hours scheduled for each provider within a specific date range. You can copy and paste this directly into your Open Dental User Query window.

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

SELECT 
    p.Abbr AS 'Provider',
    COUNT(a.AptNum) AS 'Total Appts',
    SUM(LENGTH(a.Pattern) * 5) / 60 AS 'Total Hours Scheduled'
FROM appointment a
INNER JOIN provider p ON a.ProvNum = p.ProvNum
WHERE a.AptDateTime >= @FromDate 
  AND a.AptDateTime <= @ToDate
  AND a.AptStatus IN (1, 2) -- 1=Scheduled, 2=Complete
GROUP BY p.ProvNum
ORDER BY 'Total Hours Scheduled' DESC;

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

• Provider: The abbreviation of the provider as set up in your Provider list.
• Total Appts: The count of all appointments marked as "Scheduled" or "Complete" for that provider within your chosen date range.
• Total Hours Scheduled: This calculates the total time booked. In Open Dental, the Pattern column in the appointment table uses a character for every 5-minute increment (e.g., a 30-minute appointment has a pattern length of 6). We multiply the length by 5 to get minutes, then divide by 60 to show the result in hours.

How to Customize

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

• Change the Date Range: Modify the SET @FromDate and SET @ToDate lines at the top of the query. Ensure you keep the YYYY-MM-DD format.
• Filter by Clinic: If you use multiple clinics, add a line to the WHERE clause:
  AND a.ClinicNum = 1 (Replace 1 with your specific Clinic Number).
• Exclude Completed Appointments: If you only want to see future scheduled time, change a.AptStatus IN (1, 2) to a.AptStatus = 1.

Variations

If you want to focus specifically on your hygiene department, you can filter the results to only include hygienists. Add this line to the WHERE clause:

AND p.IsSecondary = 1

(Note: This assumes your hygienists are marked as secondary providers in your system. Verify your provider setup in Lists > Providers.)

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.