Membership Plan Patient List Query

If you are struggling to keep track of which patients are currently enrolled in your in-house membership plan, this query will generate a clean list for you. It identifies active patients who have been assigned a specific fee schedule often used for membership plan tracking.

The Query

This query assumes you have set up a specific fee schedule for your membership plan (e.g., named "Membership Plan"). You will need to replace 'Membership Plan' in the query below with the exact name of the fee schedule you use in your practice.

/* Replace 'Membership Plan' with the exact name of your fee schedule */
SET @FeeSchedName = 'Membership Plan';

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.Email, 
    fs.Description AS FeeSchedule
FROM patient p
INNER JOIN feesched fs ON p.FeeSched = fs.FeeSchedNum
WHERE p.PatStatus = 0
AND fs.Description = @FeeSchedName
ORDER BY 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. Change 'Membership Plan' in the first line to match the exact name of your membership fee schedule.
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: The home phone number on file for the patient.
• Email: The email address on file for the patient.
• FeeSchedule: Confirms the fee schedule assigned to the patient, ensuring they are correctly categorized in your system.

How to Customize

You can easily narrow down your results by adding filters to the WHERE clause.

• Filter by a specific provider: If you only want to see members assigned to a specific provider, add this line before the ORDER BY clause:
  AND p.PriProv = [ProviderNumber] (Replace [ProviderNumber] with the actual number of the provider).
• Filter by clinic: If you have multiple locations and only want to see members for one, add this line:
  AND p.ClinicNum = [ClinicNumber] (Replace [ClinicNumber] with the ID of your clinic).

Variations

If you want to see a list of all patients and their assigned fee schedules to audit who is on which plan, you can remove the AND fs.Description = @FeeSchedName line. This will show every active patient and the fee schedule attached to their account, which is useful for identifying patients who might have been assigned the wrong schedule by mistake.

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.