Insurance Verification Due Query

If you are struggling to keep track of which patients have upcoming appointments but haven't had their insurance benefits or eligibility verified recently, this query is for you. While Open Dental has a built-in Insurance Verification List, this open dental insurance verification due query allows you to pull a custom list of patients with upcoming appointments whose insurance plans have not been verified within a specific number of days.

The Query

This query identifies active patients with appointments in the future who have an insurance plan that has not been verified in the last 90 days.

/* Change the number of days below to adjust the verification threshold */
SET @DaysSinceVerified = 90;
/* Change the number of days below to look further into the future */
SET @DaysIntoFuture = 30;

SELECT 
    p.LName, 
    p.FName, 
    a.AptDateTime, 
    c.CarrierName, 
    ip.DateLastVerified AS 'LastVerifiedDate'
FROM patient p
INNER JOIN appointment a ON p.PatNum = a.PatNum
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
INNER JOIN insplan ip ON pp.PlanNum = ip.PlanNum
INNER JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE p.PatStatus = 0
  AND a.AptStatus = 1
  AND a.AptDateTime BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL @DaysIntoFuture DAY)
  AND (ip.DateLastVerified < DATE_SUB(NOW(), INTERVAL @DaysSinceVerified DAY) 
       OR ip.DateLastVerified IS NULL)
ORDER BY a.AptDateTime;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• LName / FName: The patient's last and first name.
• AptDateTime: The date and time of the upcoming appointment.
• CarrierName: The name of the insurance company associated with the patient's plan.
• LastVerifiedDate: The last date the insurance plan was marked as verified in the Edit Insurance Plan window. If this column is blank, it means the plan has never been verified in Open Dental.

How to Customize

You can easily adjust the criteria to fit your office's workflow:

• Change the Verification Threshold: Look for the line SET @DaysSinceVerified = 90;. Change 90 to 60 or 180 depending on how often your office requires re-verification.
• Change the Appointment Window: Look for the line SET @DaysIntoFuture = 30;. Change 30 to 7 if you only want to see patients coming in within the next week.

Variations

If you only want to see patients with a specific insurance carrier, you can add a line to the WHERE clause:

  AND c.CarrierName LIKE '%Delta Dental%'

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.