Net Production Query for Open Dental

If you need to see your practice's actual net production for a specific period, including all adjustments and insurance write-offs, this report provides the data you need. This open dental net production query helps practice owners and managers move beyond simple gross production numbers to understand what is actually being realized.

The Query

Copy and paste the following code into your User Query window. You can adjust the FromDate and ToDate variables at the top to match the period you wish to analyze.

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

SELECT 
    SUM(pl.ProcFee * pl.UnitQty) AS GrossProduction,
    IFNULL(SUM(adj.AdjAmt), 0) AS TotalAdjustments,
    IFNULL(SUM(cp.WriteOff), 0) AS TotalWriteOffs,
    (SUM(pl.ProcFee * pl.UnitQty) + SUM(IFNULL(adj.AdjAmt, 0)) - SUM(IFNULL(cp.WriteOff, 0))) AS NetProduction
FROM procedurelog pl
LEFT JOIN adjustment adj ON pl.ProcNum = adj.ProcNum
LEFT JOIN claimproc cp ON pl.ProcNum = cp.ProcNum AND cp.Status IN (0, 1, 7)
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2;

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

• GrossProduction: The total dollar amount of all completed procedures (ProcStatus = 2) within your selected date range, based on your fee schedule.
• TotalAdjustments: The sum of all adjustments linked to those specific procedures. Note that this will include both positive and negative adjustments.
• TotalWriteOffs: The sum of insurance write-offs associated with these procedures. This pulls from the claimproc table where the status indicates an estimate or received claim.
• NetProduction: The final calculation: Gross Production plus/minus Adjustments, minus Write-offs. This represents the "real" production value for the period.

How to Customize

You can easily modify this query to fit your specific reporting needs:

• Change the Date Range: Modify the dates in the first two lines. For example, to look at February 2026, change the lines to:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';
• Filter by Provider: If you want to see net production for a specific provider, add a line to the WHERE clause:
  AND pl.ProvNum = 1
  (Replace '1' with the actual ProvNum found in the Provider Setup list).

Variations

If you want to see this data broken down by individual provider rather than a practice total, you can modify the SELECT statement to group the results:

SELECT 
    p.Abbr AS Provider,
    SUM(pl.ProcFee * pl.UnitQty) AS GrossProduction,
    IFNULL(SUM(adj.AdjAmt), 0) AS TotalAdjustments,
    IFNULL(SUM(cp.WriteOff), 0) AS TotalWriteOffs,
    (SUM(pl.ProcFee * pl.UnitQty) + SUM(IFNULL(adj.AdjAmt, 0)) - SUM(IFNULL(cp.WriteOff, 0))) AS NetProduction
FROM procedurelog pl
JOIN provider p ON pl.ProvNum = p.ProvNum
LEFT JOIN adjustment adj ON pl.ProcNum = adj.ProcNum
LEFT JOIN claimproc cp ON pl.ProcNum = cp.ProcNum AND cp.Status IN (0, 1, 7)
WHERE pl.ProcDate BETWEEN '2026-01-01' AND '2026-01-31'
AND pl.ProcStatus = 2
GROUP BY p.ProvNum;

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.