Identifying Membership Plan Patients

If you need to identify which of your active patients are currently enrolled in your in-house membership plan, this report will pull that information instantly. This open dental membership plan query is designed to help you verify enrollment status for billing or communication purposes.

The Query

This query assumes you are using the BillingType field in the Family Module to categorize your membership plan patients. You will need to identify the specific DefNum associated with your membership plan billing type.

/* Set the BillingType number for your Membership Plan here */
SET @MembershipBillingType = 1; 

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email
FROM patient p
WHERE p.PatStatus = 0
AND p.BillingType = @MembershipBillingType
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. Update the number in the SET @MembershipBillingType = 1; line to match the actual DefNum of your membership plan billing type (see below).
5. Click Submit Query.
6. The results will display in the grid below.

Understanding the Results

• PatNum: The unique identification number assigned to the patient in your database.
• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: Contact numbers to help your front desk staff reach out to these members.
• Email: The email address on file, useful if you are sending out membership renewal reminders.

How to Customize

To make this query work for your office, you must find the correct BillingType number.

1. Go to Setup > Definitions.
2. Select Billing Types from the list.
3. Look at the list of billing types. The number you need is the DefNum (you may need to enable the "Show Hidden" or "Show DefNum" options if they aren't visible).
4. Change the 1 in the query line SET @MembershipBillingType = 1; to your specific number.

If you want to filter by a specific provider, add this line before the ORDER BY clause:
AND p.PriProv = 12 (Replace 12 with the ProvNum of the provider).

Variations

If you want to see only patients who have a membership plan AND have an upcoming appointment, you can join the appointment table:

SET @MembershipBillingType = 1;

SELECT 
    p.LName, 
    p.FName, 
    a.AptDateTime
FROM patient p
INNER JOIN appointment a ON p.PatNum = a.PatNum
WHERE p.PatStatus = 0
AND p.BillingType = @MembershipBillingType
AND a.AptStatus = 1
AND a.AptDateTime >= CURDATE();

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.