If you need to see exactly how much revenue specific procedures are generating over a set period, this report provides the granular detail that standard summary reports often miss. This open dental production by procedure query allows you to analyze your practice's performance by individual procedure code, helping you identify your most profitable services.

The Query

Copy and paste the following code into your User Query window. This query filters for completed procedures (status 2) for active patients.

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

SELECT 
    pc.ProcCode, 
    pc.Descript, 
    COUNT(pl.ProcNum) AS 'Count', 
    SUM(pl.ProcFee) AS 'TotalProduction'
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
INNER JOIN patient p ON pl.PatNum = p.PatNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2
AND p.PatStatus = 0
GROUP BY pc.ProcCode, pc.Descript
ORDER BY TotalProduction 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

  • ProcCode: The ADA or custom procedure code used in your office.
  • Descript: The description associated with that procedure code in your fee schedule.
  • Count: The total number of times this specific procedure was completed within your chosen date range.
  • TotalProduction: The sum of the fees for all instances of this procedure, representing your gross production for that code.

How to Customize

You can easily modify this query to fit your specific reporting needs by changing the values in the SET statements at the top:

  • Change Date Range: Update the @FromDate and @ToDate values. Ensure you keep the YYYY-MM-DD format.
  • Filter by Provider: If you want to see production for a specific provider, add this line before the GROUP BY clause:
    AND pl.ProvNum = 1 (Replace 1 with the actual ProvNum from your provider list).
  • 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 for your desired location).

Variations

If you want to see production for a specific category of procedures (like hygiene), you can add a filter for the procedure category.

Filter by Procedure Category:
Add this line before the GROUP BY clause:
AND pc.ProcCat = 1
(Note: You will need to check your Procedure Categories setup in Open Dental to find the correct ID number for your hygiene category.)

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.