Outstanding Insurance Claims Over 30 Days

If you are tired of manually digging through the aging report to find claims that have been sitting in limbo for over a month, this tool is for you. This open dental outstanding claims query identifies all claims sent more than 30 days ago that have not yet been marked as received, allowing your team to follow up on unpaid balances efficiently.

The Query

Copy and paste the following code into your User Query window.

SET @DaysOld = 30;
SET @DateLimit = DATE_SUB(CURDATE(), INTERVAL @DaysOld DAY);

SELECT 
    c.DateSent,
    p.LName,
    p.FName,
    ca.CarrierName,
    c.ClaimFee AS 'Total Claim Amount',
    c.InsPayAmt AS 'Amount Paid',
    (c.ClaimFee - c.InsPayAmt) AS 'Remaining Balance'
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 c.DateSent < @DateLimit
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 these results as a CSV file for Excel if you prefer to work with them in a spreadsheet.

Understanding the Results

• DateSent: The date the claim was originally submitted to the insurance carrier.
• LName/FName: The patient's last and first name.
• CarrierName: The name of the insurance company.
• Total Claim Amount: The original fee billed on the claim.
• Amount Paid: Any partial payments already recorded against this claim.
• Remaining Balance: The difference between the total claim and what has been paid, representing the outstanding amount you are waiting to collect.

How to Customize

You can easily adjust the timeframe for your follow-up process by changing the first line of the query.

• Change the days: If you want to see claims older than 60 days instead of 30, change the first line to:
  SET @DaysOld = 60;
• Filter by Clinic: If you are a multi-location practice and only want to see claims for a specific clinic, add this line before the ORDER BY clause (replace 1 with your specific ClinicNum):
  AND c.ClinicNum = 1

Variations

If you want to focus only on high-dollar claims to prioritize your collections efforts, you can add a filter to the end of the query to only show claims with a remaining balance over $500:

-- Add this before the ORDER BY line
AND (c.ClaimFee - c.InsPayAmt) > 500

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.