Open Dental Expired Insurance Query

If you are tired of discovering that a patient's insurance plan expired months ago only after a claim is denied, this report is for you. This open dental expired insurance query identifies active patients who have an insurance plan with a termination date in the past, allowing your front desk team to proactively update coverage before the next appointment.

The Query

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

SET @TargetDate = 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 < @TargetDate
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 the "Export" button to save these results as a CSV file if you wish to work with them in Excel.

Understanding the Results

• LName / FName: The patient's name so you know who to contact.
• HmPhone / WirelessPhone: Contact information to reach out and request updated insurance cards.
• 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.

How to Customize

You can easily adjust this report to look for plans expiring in the future (e.g., within the next 30 days) to get ahead of the problem.

• To find plans expiring in the next 30 days: Change the WHERE clause line AND sub.DateTerm < @TargetDate to:
  AND sub.DateTerm BETWEEN @TargetDate AND DATE_ADD(@TargetDate, INTERVAL 30 DAY)
• To filter by a specific provider: Add this line to the WHERE clause:
  AND p.PriProv = [InsertProviderNumberHere] (You can find the provider number in the Setup > Providers window).

Variations

If you only want to see patients who have an appointment scheduled in the near future but have expired insurance, you can join the appointment table to the query. This helps prioritize your calling list by focusing on patients who are actually coming into the office 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.