Track Insurance Payment Speed by Carrier

If you are tired of wondering why your cash flow feels sluggish, this report identifies exactly how long it takes for specific insurance companies to pay your claims. By using this open dental insurance payment speed query, you can pinpoint which carriers are consistently slow, allowing your front desk team to prioritize follow-ups more effectively.

The Query

Copy and paste the following code into your User Query window. This query calculates the average number of days between the date a claim was sent and the date it was received for all claims marked as "Received" within your specified date range.

/* 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 the carrier name, the number of claims processed, and the average days it took to receive payment.

Understanding the Results

• CarrierName: The name of the insurance company as entered in your database.
• TotalClaims: The total number of claims received for that carrier within your chosen date range.
• AvgDaysToPay: The average number of days elapsed from the date the claim was sent (DateSent) to the date it was marked as received (DateReceived).

How to Customize

You can easily adjust the report to fit your specific needs by modifying the lines at the top of the query:

• Change the Date Range: Modify the @FromDate and @ToDate lines. For example, to look at the first quarter of 2026, change them to '2026-01-01' and '2026-03-31'.
• 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, which you can find in Setup > Clinics).

Variations

If you want to see if specific providers are associated with slower payments, you can add the provider's name to the report. Change the SELECT line to include p.LName and add an INNER JOIN provider p ON cl.ProvBill = p.ProvNum after the carrier join.

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.