Top 20 Procedures by Revenue

If you want to know 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 provides a ranked list of your top 20 procedures based on the total fees billed, helping you make data-driven decisions about your service offerings.

The Query

Copy and paste the following code into your User Query window. You can adjust the FromDate and ToDate variables to analyze any specific time period.

SET @FromDate = '2026-01-01';
SET @ToDate = '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 BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2
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 procedure code (e.g., D0150, D1110).
• Description: The plain-English name of the procedure as defined in your procedure code list.
• Count: The total number of times this specific procedure was completed within your selected date range.
• TotalRevenue: The sum of the ProcFee for all instances of this procedure, representing your gross production for that service.

How to Customize

You can easily tailor this report to fit your specific needs by modifying the variables at the top of the query:

• Change the 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 top procedures for a specific provider, add a line to the WHERE clause:
  AND pl.ProvNum = 1 (Replace 1 with the actual ProvNum of the provider).
• Filter by Clinic: If you have multiple locations, you can isolate one clinic by adding this to the WHERE clause:
  AND pl.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to see which procedures are being treatment planned most often (to identify potential future revenue), change pl.ProcStatus = 2 to pl.ProcStatus = 1.

If you want to see the top procedures by quantity rather than revenue, change the ORDER BY line to:
ORDER BY Count DESC

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.