Open Dental Write-offs by Carrier Query

If you need to see exactly how much revenue is being written off per insurance carrier to evaluate your PPO participation, this report provides the data you need. This open dental writeoffs by carrier query allows you to aggregate all insurance write-offs within a specific date range, helping you identify which plans are impacting your bottom line the most.

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-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
AND cp.Status IN (1, 4) -- 1=Received, 4=Supplemental
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, showing the carrier name and the total dollar amount written off for that period.

Understanding the Results

• CarrierName: The name of the insurance company as entered in your Insurance Plan setup.
• TotalWriteOff: The sum of all insurance write-offs associated with that carrier for the selected date range. This represents the difference between your office fee and the allowed amount agreed upon with the insurance plan.

How to Customize

You can easily modify this report to fit your specific needs by changing the variables at the top of the query:

• 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, which you can find in Setup > Clinics).

Variations

Including Provider Information

If you want to see write-offs broken down by both carrier and the treating provider, you can modify the SELECT and GROUP BY lines:

SELECT 
    c.CarrierName,
    p.LName AS Provider,
    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
INNER JOIN provider p ON cp.ProvNum = p.ProvNum
WHERE cp.WriteOff > 0
AND cp.DateCP BETWEEN @FromDate AND @ToDate
AND cp.Status IN (1, 4)
GROUP BY c.CarrierName, p.LName
ORDER BY TotalWriteOff DESC;

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.