Tracking Outstanding Insurance Claims Over 30 Days

If you are struggling to keep track of insurance payments that are falling through the cracks, this report will help you identify exactly which claims have been sitting unpaid for over a month. This open dental outstanding claims query provides a clean list of pending claims, allowing your front desk team to prioritize follow-up calls and improve your practice's cash flow.

The Query

Copy and paste the following code into your User Query window. This query looks for claims with a status of "Sent" that have not yet been marked as "Received."

/* Set the number of days to look back */
SET @DaysPast = 30;

SELECT 
    c.DateSent,
    p.LName,
    p.FName,
    ca.CarrierName,
    c.ClaimFee,
    c.InsPayAmt AS ExpectedPay,
    DATEDIFF(CURDATE(), c.DateSent) AS DaysOutstanding
FROM claim c
INNER JOIN patient p ON c.PatNum = p.PatNum
INNER JOIN insplan ip ON c.PlanNum = ip.PlanNum
INNER JOIN carrier ca ON ip.CarrierNum = ca.CarrierNum
WHERE c.ClaimStatus = 'S'
AND DATEDIFF(CURDATE(), c.DateSent) > @DaysPast
ORDER BY c.DateSent ASC;

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. You can click the "Export" button to save this list as a CSV file if you prefer to work in Excel.

Understanding the Results

• DateSent: The date the claim was originally transmitted to the insurance carrier.
• LName / FName: The patient's last and first name.
• CarrierName: The name of the insurance company associated with the claim.
• ClaimFee: The total dollar amount billed on the claim.
• ExpectedPay: The amount Open Dental estimates the insurance will pay for this claim.
• DaysOutstanding: The total number of days that have passed since the claim was sent.

How to Customize

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

• Change the Date Range: If you want to see claims older than 60 days instead of 30, change the first line of the query:
  SET @DaysPast = 60;
• Filter by Clinic: If your practice has multiple locations and you only want to see claims for one, add this line before the ORDER BY clause:
  AND c.ClinicNum = 1
  (Replace "1" with your specific ClinicNum, which you can find in Setup > Clinics).

Variations

If you want to focus only on high-dollar claims that are overdue, you can add a filter for the claim amount. Add this line before the ORDER BY clause:

AND c.ClaimFee > 500

This will filter the list to show only claims where the total fee is greater than $500, helping you prioritize the most significant outstanding balances first.

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.