If you are trying to determine which services are driving the most revenue for your practice, you need a clear way to analyze your production data. This open dental top procedures query allows you to see exactly which procedure codes are bringing in the most money over a specific time period, helping you make informed decisions about your practice's focus.

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 the timeframe you wish to analyze.

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

SELECT 
    pc.ProcCode AS 'Code',
    pc.Descript AS 'Description',
    COUNT(pl.ProcNum) AS 'Count',
    SUM(pl.ProcFee) AS 'TotalRevenue'
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
WHERE pl.ProcDate >= @StartDate 
AND pl.ProcDate <= @EndDate
AND pl.ProcStatus = 2 -- Only includes 'Complete' procedures
GROUP BY pc.CodeNum
ORDER BY TotalRevenue DESC
LIMIT 20;

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

  • Code: The ADA or custom procedure code used in your system.
  • Description: The name or description associated with that procedure code in your procedurecode table.
  • Count: The total number of times this specific procedure was marked as "Complete" within your selected date range.
  • TotalRevenue: The sum of the ProcFee for all instances of this procedure. This represents the gross production amount before any adjustments or insurance write-offs.

How to Customize

You can easily modify this query to better suit your needs:

  • Change the Date Range: Modify the dates inside the single quotes for @StartDate and @EndDate. Ensure you keep the YYYY-MM-DD format.
  • Filter by Provider: If you want to see the top procedures for a specific provider, add this line before the GROUP BY clause:
    AND pl.ProvNum = 1 (Replace 1 with the actual ProvNum of the provider).
  • Filter by Clinic: If you have multiple locations, add this line before the GROUP BY clause:
    AND pl.ClinicNum = 1 (Replace 1 with the actual ClinicNum of the location).

Variations

If you want to see the top procedures by volume (number of times performed) rather than total revenue, simply change the ORDER BY line at the end of the query:

Change: ORDER BY TotalRevenue DESC
To: ORDER BY Count DESC

This is helpful if you want to identify high-frequency procedures that might be candidates for bulk ordering supplies or streamlining clinical workflows.

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.