Active Patients by Insurance Carrier

If you need to identify which active patients are covered by a specific insurance carrier for marketing or administrative purposes, this report provides the data you need. Running an open dental patients by insurance query allows you to pull a clean list of patients, their contact information, and their current insurance plan details directly from your database.

The Query

Copy and paste the following code into your User Query window. This query filters for active patients and joins the necessary tables to display the carrier name alongside the patient's name and contact details.

/* Query to list active patients by insurance carrier */
SET @CarrierName = '%'; -- Use '%' for all, or enter a specific name like 'Delta Dental'

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    c.CarrierName, 
    ip.GroupName
FROM patient p
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
INNER JOIN inssub ins ON pp.InsSubNum = ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum = ip.PlanNum
INNER JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE p.PatStatus = 0
AND c.CarrierName LIKE @CarrierName
ORDER BY c.CarrierName, p.LName, p.FName;

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. If you want to filter for a specific carrier, change the first line from SET @CarrierName = '%'; to SET @CarrierName = 'Name of Carrier'; (keep the single quotes).
5. Click Submit Query.
6. The results will display in the grid below.

Understanding the Results

• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: The contact numbers on file for the patient.
• Email: The email address listed in the Family Module.
• CarrierName: The name of the insurance company associated with the patient's active plan.
• GroupName: The specific group name or plan identifier associated with that insurance policy.

How to Customize

You can easily modify this query to narrow down your results:

• Filter by specific carrier: As noted in the query, change the % inside the single quotes to the exact name of the insurance carrier you are looking for.
• Filter by Clinic: If you have multiple locations and only want to see patients for one, add this line before the ORDER BY clause: AND p.ClinicNum = 1 (replace 1 with your specific Clinic Number).
• Include Only Primary Insurance: If you want to exclude secondary insurance plans, add AND pp.Ordinal = 1 to the WHERE clause.

Variations

If you want to see only patients who have an email address on file (useful for digital newsletters), add this line to the WHERE clause: AND p.Email != ''.

If you are looking to identify patients with a specific insurance plan to notify them of a change in network status, you can add AND ip.GroupName = 'YourGroupName' to the WHERE clause to filter by the specific group number or name.

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.