Open Dental Average Production per Visit Query

If you want to understand the financial health of your practice, you need to know how much revenue each patient visit generates on average. This open dental average production per visit query allows you to calculate your daily production efficiency by dividing total completed production by the number of unique patient visits within a specific timeframe.

The Query

Copy and paste the following code into your User Query window. You can adjust the StartDate and EndDate variables at the top to fit your reporting needs.

SET @StartDate = '2026-01-01';
SET @EndDate = '2026-03-31';

SELECT 
    TotalProduction,
    TotalVisits,
    ROUND(TotalProduction / TotalVisits, 2) AS AvgProdPerVisit
FROM (
    SELECT 
        SUM(pl.ProcFee) AS TotalProduction,
        COUNT(DISTINCT pl.AptNum) AS TotalVisits
    FROM procedurelog pl
    WHERE pl.ProcStatus = 2
    AND pl.ProcDate BETWEEN @StartDate AND @EndDate
    AND pl.AptNum > 0
) AS Summary;

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

• TotalProduction: The sum of all procedure fees for procedures marked as "Complete" (ProcStatus 2) within your selected date range.
• TotalVisits: The count of unique appointment numbers (AptNum) associated with those completed procedures. This ensures that if a patient has multiple procedures done in one sitting, it is counted as a single visit.
• AvgProdPerVisit: The final calculation showing the average dollar amount produced per patient visit.

How to Customize

You can easily modify this query to look at specific segments of your practice:

• Change Date Range: Modify the dates in the SET statements at the top. Ensure they remain in the 'YYYY-MM-DD' format.
• Filter by Provider: If you want to see the average for a specific provider, add a WHERE clause inside the subquery:
  AND pl.ProvNum = 1 (Replace 1 with the specific Provider Number found in your Provider setup).
• Filter by Clinic: If you have multiple locations, add this line inside the subquery:
  AND pl.ClinicNum = 1 (Replace 1 with your specific Clinic Number).

Variations

If you want to focus specifically on hygiene production, you can filter the query to only include procedures performed by your hygienist. Add this line inside the subquery:

AND pl.ProvNum IN (SELECT ProvNum FROM provider WHERE IsHygienist = 1)

Note: This assumes your providers are correctly flagged as hygienists in the Provider setup.

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.