Net Production Query with Adjustments and Writeoffs

If you need a precise breakdown of your practice's financial performance that accounts for both adjustments and insurance write-offs, this query provides the data you need. It calculates your net production by taking your gross production, adding or subtracting adjustments, and subtracting insurance write-offs for a specific date range.

The Query

Copy and paste the following code into your User Query window. You can change the dates in the SET statements at the top to match the period you want to analyze.

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

SELECT 
    'Gross Production' AS Category, 
    SUM(ProcFee * UnitQty) AS Amount
FROM procedurelog
WHERE ProcDate BETWEEN @FromDate AND @ToDate
  AND ProcStatus = 2 -- Complete
UNION ALL
SELECT 
    'Adjustments' AS Category, 
    SUM(AdjAmt) AS Amount
FROM adjustment
WHERE AdjDate BETWEEN @FromDate AND @ToDate
UNION ALL
SELECT 
    'Insurance Write-offs' AS Category, 
    SUM(WriteOff) AS Amount
FROM claimproc
WHERE DateCP BETWEEN @FromDate AND @ToDate
  AND Status = 1 -- Received
UNION ALL
SELECT 
    'Net Production' AS Category, 
    (SELECT SUM(ProcFee * UnitQty) FROM procedurelog WHERE ProcDate BETWEEN @FromDate AND @ToDate AND ProcStatus = 2)
    + (SELECT SUM(AdjAmt) FROM adjustment WHERE AdjDate BETWEEN @FromDate AND @ToDate)
    - (SELECT SUM(WriteOff) FROM claimproc WHERE DateCP BETWEEN @FromDate AND @ToDate AND Status = 1) 
    AS Amount;

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.

Understanding the Results

• Gross Production: The total sum of fees for all procedures marked as "Complete" within your selected date range.
• Adjustments: The total sum of all adjustments (both positive and negative) posted to patient accounts within the date range.
• Insurance Write-offs: The total amount of insurance write-offs associated with claims marked as "Received" during the date range.
• Net Production: The final calculated figure: Gross Production + Adjustments - Insurance Write-offs.

How to Customize

To change the date range, simply edit the two lines at the very top of the query:

• Change '2026-01-01' to your desired start date.
• Change '2026-01-31' to your desired end date.

Ensure you keep the single quotes around the dates and use the YYYY-MM-DD format.

Variations

If you want to see this data for a specific provider only, you would need to add a WHERE ProvNum = X clause (where X is the provider's number) to each of the SELECT statements within the query. Note that this requires knowing the specific ProvNum from your provider list.

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.