Open Dental Production by Procedure Query

If you need to see exactly how much revenue specific procedures are generating over a set period, this report provides the granular detail you need. By using an open dental production by procedure query, you can identify your most profitable services and track the performance of specific treatment categories.

The Query

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

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

SELECT 
    pc.ProcCode, 
    pc.Descript AS 'Description', 
    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
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.
• Description: The name of the procedure as defined in your Procedure Code list.
• Count: The total number of times this specific procedure was completed during your selected date range.
• TotalProduction: The sum of the fees for all instances of this procedure, representing your total production for that code.

How to Customize

You can easily modify this report to fit your specific needs by changing the values in the first two lines 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 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.

To focus only on hygiene, you could add AND pc.ProcCat = 1 (verify your specific ProcCat ID in your Procedure Code setup). Alternatively, if you want to see which procedures are being treatment planned but not yet completed, change pl.ProcStatus = 2 to pl.ProcStatus = 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.