Active Patients by Insurance Carrier

If you need to identify which insurance carriers represent the largest portion of your active patient base, this report provides the data you need. This open dental patients by insurance query allows you to see exactly which active patients are attached to which insurance plans, helping you manage your payer mix effectively.

The Query

Copy and paste the following code into the User Query window. This query filters for patients with an active status (0) and joins the necessary tables to pull the carrier name and patient details.

/* Set the status to 0 for Active Patients */
SET @PatStatus = 0;

SELECT 
    c.CarrierName,
    p.LName,
    p.FName,
    p.HmPhone,
    p.Email
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 = @PatStatus
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. Click Submit Query.
5. The results will display in the grid below, which you can then export to Excel if needed.

Understanding the Results

• CarrierName: The name of the insurance company as entered in your insurance setup.
• LName / FName: The last and first name of the patient.
• HmPhone: The home phone number on file for the patient.
• Email: The email address on file for the patient.

How to Customize

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

• Filter by a specific carrier: If you only want to see patients for one insurance company, add this line before the ORDER BY clause:
  AND c.CarrierName LIKE '%Delta Dental%'
• Include Inactive Patients: If you want to see all patients regardless of status, change the first line to SET @PatStatus = -1; (this removes the status filter).

Variations

If you want to see only patients who have a primary insurance plan (ignoring secondary plans), you can add a filter for the Ordinal column in the patplan table:

/* Add this to the WHERE clause */
AND pp.Ordinal = 1

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.