Insurance Verification Due Query

If you are tired of scrambling to verify insurance benefits the morning of an appointment, this report will help you get ahead. This open dental insurance verification due query identifies all active patients with appointments scheduled within a specific date range so your front desk team can verify coverage in advance.

The Query

Copy and paste the following code into your User Query window. You can adjust the date range in the SET statements at the top to look ahead at the next week or month of appointments.

/* Set your date range here */
SET @FromDate = '2026-04-01';
SET @ToDate = '2026-04-30';

SELECT 
    a.AptDateTime AS 'Appt Date',
    p.LName AS 'Last Name',
    p.FName AS 'First Name',
    p.HmPhone AS 'Home Phone',
    c.CarrierName AS 'Insurance Carrier',
    ip.GroupName AS 'Group Name'
FROM appointment a
INNER JOIN patient p ON a.PatNum = p.PatNum
LEFT JOIN patplan pp ON p.PatNum = pp.PatNum AND pp.Ordinal = 1
LEFT JOIN inssub ins ON pp.InsSubNum = ins.InsSubNum
LEFT JOIN insplan ip ON ins.PlanNum = ip.PlanNum
LEFT JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE a.AptDateTime >= @FromDate 
  AND a.AptDateTime <= @ToDate
  AND a.AptStatus IN (1, 4)
  AND p.PatStatus = 0
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 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 this as a CSV file for Excel if you prefer to print it as a checklist.

Understanding the Results

• Appt Date: The date and time the patient is scheduled to be in your office.
• Last Name / First Name: The patient's name for easy identification in the Family Module.
• Home Phone: The primary contact number on file to reach the patient if you need to request updated insurance cards.
• Insurance Carrier: The name of the insurance company associated with the patient's primary plan.
• Group Name: The specific employer group or plan name, which helps when calling for verification.

How to Customize

You can easily modify this report to fit your office's workflow:

• Change the Date Range: Modify the @FromDate and @ToDate lines at the top of the query. For example, change '2026-04-01' to '2026-05-01' to look at next month's schedule.
• Filter by Provider: If you only want to verify patients for a specific provider, add this line before the ORDER BY clause:
  AND a.ProvNum = 1 (Replace 1 with the actual ProvNum from your provider list).

Variations

If you want to focus only on patients who are new to the practice, you can add this line to the WHERE clause:
AND a.IsNewPatient = 1

If you want to exclude patients who have already had their insurance verified (assuming you use the "Insurance Verification" status in the Appointment Module), you would need to join the appointment table with the insverify table, though this requires ensuring your staff is consistently updating the verification status within the Appointments Module.

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.