If you are trying to determine the financial efficiency of your daily schedule, you need to know how much revenue each patient visit generates on average. This open dental average production per visit query provides a clear look at your production numbers, helping you identify trends in your practice's performance.

The Query

This query calculates the total completed production and divides it by the number of unique completed appointments within your chosen date range.

/* Set your date range here */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-03-31';

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

How to Run This Query

  1. In Open Dental, go to Reports in the Main Menu.
  2. Click User Query.
  3. Paste the query into the text box.
  4. Click Submit Query.
  5. The results will display in the grid below.

Understanding the Results

  • TotalVisits: This is the count of unique appointments that had at least one completed procedure attached to them within the specified date range.
  • TotalProduction: This is the sum of the ProcFee for all procedures marked as "Complete" (ProcStatus = 2) during the selected timeframe.
  • AvgProdPerVisit: This is the mathematical average, calculated by dividing the Total Production by the Total Visits.

How to Customize

You can easily adjust the scope of this report by modifying the SET statements at the top of the query.

  • Change Date Range: Update the dates inside the single quotes for @FromDate and @ToDate. Ensure you keep the YYYY-MM-DD format.
  • Filter by Provider: If you want to see the average for a specific provider, add a line to the WHERE clause:
    AND ProvNum = 1 (Replace 1 with the actual ProvNum from your provider list).
  • Filter by Clinic: If you use Clinics, you can isolate a specific location by adding:
    AND ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

Hygiene vs. Doctor Production

If you want to see the average production specifically for hygiene visits, you can join the appointment table to filter by the IsHygiene flag:

SELECT 
    COUNT(DISTINCT p.AptNum) AS HygieneVisits,
    SUM(p.ProcFee) AS HygieneProduction,
    ROUND(SUM(p.ProcFee) / COUNT(DISTINCT p.AptNum), 2) AS AvgHygieneProd
FROM procedurelog p
INNER JOIN appointment a ON p.AptNum = a.AptNum
WHERE p.ProcDate BETWEEN '2026-01-01' AND '2026-03-31'
AND p.ProcStatus = 2
AND a.IsHygiene = 1;

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.