Find Patients with Expired Insurance

If you are tired of discovering a patient’s insurance has expired only after a claim is denied, this report is for you. This open dental expired insurance query identifies all active patients who have an insurance plan with a termination date in the past, allowing your front desk team to proactively request updated coverage information.

The Query

Copy and paste the following code into your User Query window. This query looks for active patients (PatStatus = 0) who have an insurance subscription with a DateTerm that is earlier than today's date.

SET @Today = CURDATE();

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    c.CarrierName, 
    sub.DateTerm AS 'ExpirationDate'
FROM patient p
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
INNER JOIN inssub sub ON pp.InsSubNum = sub.InsSubNum
INNER JOIN insplan ip ON sub.PlanNum = ip.PlanNum
INNER JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE p.PatStatus = 0
AND sub.DateTerm IS NOT NULL 
AND sub.DateTerm < @Today
ORDER BY sub.DateTerm 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. You can click Export to save the list as a CSV file if you wish to print it or work in Excel.

Understanding the Results

• LName/FName: The patient's name.
• HmPhone/WirelessPhone: Contact information to reach the patient for updated insurance details.
• CarrierName: The name of the insurance company associated with the expired plan.
• ExpirationDate: The specific date the insurance plan terminated, confirming why it is flagged in this report.

How to Customize

You can easily adjust this report to fit your specific needs by modifying the WHERE clause.

• Filter by Clinic: If you have multiple locations and only want to see one, add this line to the WHERE clause:
  AND p.ClinicNum = 1 (Replace 1 with your specific Clinic Number).
• Look for Upcoming Expirations: If you want to see insurance expiring in the next 30 days instead of just past expirations, change the last line of the WHERE clause to:
  AND sub.DateTerm BETWEEN @Today AND DATE_ADD(@Today, INTERVAL 30 DAY)

Variations

If you want to narrow this list down to only patients who have an upcoming appointment, you can add a join to the appointment table:

-- Add this join to the query above
INNER JOIN appointment a ON p.PatNum = a.PatNum
WHERE p.PatStatus = 0
AND sub.DateTerm < @Today
AND a.AptDateTime > @Today

This variation is particularly useful for the front desk to prioritize calling patients who are scheduled to come in soon.

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.