Track Insurance Payment Speed by Carrier

If you are tired of wondering why certain insurance companies take months to reimburse your office, this report provides the data you need to identify the bottlenecks. By using this open dental insurance payment speed query, you can calculate the average number of days between when a claim is sent and when it is received, broken down by carrier.

The Query

Copy and paste the following code into your User Query window. This query looks at claims marked as "Received" within the date range you specify.

/* Set your date range here */
SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-31';

SELECT 
    c.CarrierName,
    COUNT(cl.ClaimNum) AS TotalClaims,
    ROUND(AVG(DATEDIFF(cl.DateReceived, cl.DateSent)), 1) AS AvgDaysToPay
FROM claim cl
INNER JOIN insplan ip ON cl.PlanNum = ip.PlanNum
INNER JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE cl.ClaimStatus = 'R'
  AND cl.DateReceived BETWEEN @FromDate AND @ToDate
  AND cl.DateSent <= cl.DateReceived
GROUP BY c.CarrierName
ORDER BY AvgDaysToPay 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 each carrier and their average payment speed.

Understanding the Results

• CarrierName: The name of the insurance company as entered in your system.
• TotalClaims: The number of claims received for that carrier within your selected date range.
• AvgDaysToPay: The average number of calendar days it took for the insurance company to pay the claim, calculated from the date the claim was sent to the date it was marked as received.

How to Customize

You can easily adjust the parameters of this report to fit your needs:

• Change the Date Range: Modify the dates in the SET statements at the top of the query. For example, change '2025-01-01' to '2026-01-01' to look at more recent data.
• Filter by Clinic: If you have multiple locations and want to see data for only one, add this line before the GROUP BY clause:
  AND cl.ClinicNum = 1
  (Replace '1' with your specific ClinicNum found in Setup > Clinics).

Variations

If you want to see if specific providers are associated with slower payment times, you can modify the SELECT and GROUP BY lines to include cl.ProvBill. This helps identify if claims billed under specific providers are being held up more frequently than others.

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.