Tracking Unfinalized Insurance Payments

If your insurance aging report looks cluttered with claims that should have been finalized weeks ago, you need a way to isolate those specific items. This open dental unfinalized payments query helps office managers identify claims where the status is stuck or incomplete, allowing you to clean up your accounts receivable efficiently.

The Query

This query identifies claims that have been sent but are not yet marked as "Received" in your system, helping you focus on follow-ups.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-04-04';

SELECT 
    c.DateSent, 
    p.LName, 
    p.FName, 
    ca.CarrierName, 
    c.ClaimFee, 
    c.InsPayAmt AS ExpectedPay,
    c.ClaimStatus
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.DateSent BETWEEN @FromDate AND @ToDate
AND c.ClaimStatus IN ('S', 'W')
AND p.PatStatus = 0
ORDER BY c.DateSent;

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.

Understanding the Results

• DateSent: The date the claim was electronically or manually sent to the insurance carrier.
• LName/FName: The patient's last and first name.
• CarrierName: The name of the insurance company.
• ClaimFee: The total amount billed on the claim.
• ExpectedPay: The estimated insurance payment amount.
• ClaimStatus: The current status code ('S' for Sent, 'W' for Waiting to Send).

How to Customize

You can easily adjust the date range or filter by specific criteria to make the report more useful for your daily workflow.

• Change Date Range: Modify the SET @FromDate and SET @ToDate lines at the very top of the query. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Clinic: If you have multiple locations, add this line to the WHERE clause: AND c.ClinicNum = 1 (replace 1 with your specific Clinic Number).
• Filter by Provider: To see claims for a specific provider, add: AND c.ProvTreat = 12 (replace 12 with the specific ProvNum).

Variations

If you want to see only claims that have been sitting for a long time, you can change the WHERE clause to look for claims sent more than 30 days ago:

-- Add this to the WHERE clause instead of the date range
AND c.DateSent < DATE_SUB(CURDATE(), INTERVAL 30 DAY)

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.