Provider Schedule Utilization Query

If you are struggling to understand how effectively your providers are utilizing their time, this open dental schedule utilization query will help you identify gaps in your daily production. By analyzing appointment patterns against completed procedures, you can see exactly how much time is being booked versus how much is actually being utilized.

The Query

This query calculates the total time units scheduled for a provider and compares it against the number of completed procedures within a specific date range.

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

SELECT 
    p.Abbr AS Provider,
    COUNT(a.AptNum) AS TotalAppointments,
    SUM(LENGTH(a.Pattern) / 2) AS TotalTimeUnits,
    (SELECT COUNT(*) 
     FROM procedurelog pl 
     WHERE pl.ProvNum = p.ProvNum 
     AND pl.ProcDate BETWEEN @FromDate AND @ToDate 
     AND pl.ProcStatus = 2) AS CompletedProcedures
FROM appointment a
INNER JOIN provider p ON a.ProvNum = p.ProvNum
WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate
AND a.AptStatus = 2 -- Only completed appointments
GROUP BY p.ProvNum
ORDER BY TotalTimeUnits 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 the text box.

Understanding the Results

• Provider: The abbreviation of the provider as set up in your provider list.
• TotalAppointments: The count of appointments marked as "Complete" (AptStatus 2) for that provider within your chosen date range.
• TotalTimeUnits: This calculates the total time allocated to those appointments. In Open Dental, the Pattern column uses one character per 10-minute increment; this query divides the length of that string by 2 to estimate the total units.
• CompletedProcedures: A count of all procedures marked as "Complete" (ProcStatus 2) linked to that provider in the same date range.

How to Customize

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

• Change the Date Range: Modify the @FromDate and @ToDate lines. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Clinic: If you have multiple locations, add a line to the WHERE clause:
  AND a.ClinicNum = 1 (Replace 1 with your specific Clinic Number).
• Exclude Hidden Providers: To ensure you are only looking at active staff, add this to the WHERE clause:
  AND p.IsHidden = 0

Variations

If you want to focus specifically on hygiene utilization, you can filter the appointments to only include those marked as hygiene:

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

If you want to see which providers have the highest volume of "Broken" appointments to identify scheduling inefficiencies, change a.AptStatus = 2 to a.AptStatus = 5.

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.