Open Dental Write-offs by Carrier Query

If you need to see exactly how much your practice is writing off for specific insurance carriers over a set period, this report provides the clarity you need. This open dental writeoffs by carrier query aggregates all insurance write-offs, allowing you to compare the impact of different plans on your bottom line.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET variables at the top to define your specific date range.

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

SELECT 
    c.CarrierName,
    SUM(cp.WriteOff) AS TotalWriteOff
FROM claimproc cp
INNER JOIN insplan ip ON cp.PlanNum = ip.PlanNum
INNER JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE cp.WriteOff > 0
AND cp.DateCP BETWEEN @FromDate AND @ToDate
GROUP BY c.CarrierName
ORDER BY TotalWriteOff 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.

Understanding the Results

• CarrierName: This is the name of the insurance company as it appears in your Manage Module under the Insurance Plans setup.
• TotalWriteOff: This is the sum of all WriteOff amounts recorded in the claimproc table for that specific carrier within your chosen date range.

How to Customize

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

• Change the Date Range: Modify the dates inside the single quotes for @FromDate and @ToDate. Ensure you keep the YYYY-MM-DD format.
• Filter by Clinic: If you have multiple locations and only want to see data for one, add this line before the GROUP BY clause:
  AND cp.ClinicNum = 1
  (Replace '1' with your specific ClinicNum found in Setup > Clinics).

Variations

Filter by Specific Carrier

If you only want to see the write-offs for one specific insurance company, add a WHERE clause filter:

-- Add this line before the GROUP BY clause
AND c.CarrierName LIKE '%Delta Dental%'

Include Procedure Details

If you need to see which procedures are driving the highest write-offs, you can join the procedurelog and procedurecode tables to the query to display the AbbrDesc of the procedures alongside the carrier name.

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.