Finding Patients Without Email Addresses

If you are trying to increase your digital communication or improve your automated recall system, you need a clean list of patients who haven't provided an email address. This open dental missing email query allows you to quickly identify active patients who are missing this vital contact information so your front desk can update their records during their next visit.

The Query

This query targets active patients (PatStatus = 0) who have an empty email field.

/* Query to find active patients with no email address */
SELECT 
    PatNum AS 'ID', 
    LName AS 'Last Name', 
    FName AS 'First Name', 
    HmPhone AS 'Home Phone', 
    WirelessPhone AS 'Cell Phone'
FROM patient
WHERE PatStatus = 0 
AND (Email = '' OR Email IS NULL)
ORDER BY LName, 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.

Understanding the Results

• ID: The unique patient number assigned by Open Dental.
• Last Name / First Name: The patient's name as entered in the Family Module.
• Home Phone / Cell Phone: These columns are included so your staff can easily call the patient to request their email address while looking at the list.

How to Customize

If you want to narrow your search to a specific group, you can modify the WHERE clause.

• Filter by Provider: If you only want to see patients assigned to a specific primary provider, add this line before the ORDER BY clause:
  AND PriProv = 1 (Replace '1' with the actual Provider Number found in Setup > Definitions > Providers).
• Filter by Clinic: If you have multiple locations and only want to check one, add this line:
  AND ClinicNum = 1 (Replace '1' with your specific Clinic Number).

Variations

If you want to focus your efforts on patients who have been seen recently, you can filter by their last visit date. This helps prioritize patients who are currently active in your practice.

/* Active patients with no email who have been seen in the last 2 years */
SELECT 
    p.PatNum, p.LName, p.FName, MAX(pl.ProcDate) AS 'Last Visit'
FROM patient p
LEFT JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.PatStatus = 0 
AND (p.Email = '' OR p.Email IS NULL)
AND pl.ProcStatus = 2
GROUP BY p.PatNum
HAVING MAX(pl.ProcDate) > (CURDATE() - INTERVAL 2 YEAR)
ORDER BY MAX(pl.ProcDate) DESC;

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.