Open Dental Overdue Recall Query

If you are struggling to identify which active patients have fallen through the cracks and are overdue for their hygiene visit, this open dental overdue recall query is the solution. It allows you to pull a clean list of patients based on their last completed hygiene appointment date, helping your front desk team prioritize their outreach efforts.

The Query

This query targets active patients who have not had a hygiene appointment within a specific timeframe. You can adjust the INTERVAL in the SET statement to define what "overdue" means for your practice (e.g., 6 months, 12 months, or 18 months).

/* Set the number of months to define "overdue" */
SET @MonthsOverdue = 12;

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    MAX(a.AptDateTime) AS LastHygieneDate
FROM patient p
INNER JOIN appointment a ON p.PatNum = a.PatNum
WHERE p.PatStatus = 0
  AND a.AptStatus = 2 /* Complete */
  AND a.IsHygiene = 1
GROUP BY p.PatNum
HAVING MAX(a.AptDateTime) < DATE_SUB(CURDATE(), INTERVAL @MonthsOverdue MONTH)
ORDER BY LastHygieneDate ASC;

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. You can click the "Export" button to save this list as a CSV file for use in Excel or your patient communication software.

Understanding the Results

• LName / FName: The patient's last and first name.
• HmPhone / WirelessPhone: Contact numbers to help your team reach out.
• Email: The email address on file for the patient.
• LastHygieneDate: The date of the most recent completed hygiene appointment found in the system. If this date is older than your defined threshold, they appear on this list.

How to Customize

You can easily modify this query to fit your specific needs:

• Change the Overdue Threshold: Change the number in the first line: SET @MonthsOverdue = 12;. If you want to see patients overdue by 18 months, simply change 12 to 18.
• Filter by Provider: If you want to see overdue patients for a specific hygienist, add this line to the WHERE clause: AND a.ProvHyg = [ProviderNumber]. (Replace [ProviderNumber] with the actual ID number of the provider found in the Provider setup).

Variations

Only Patients with Insurance

If you want to focus your retention efforts on patients who have active insurance benefits, you can add a join to the patplan table to filter out those without coverage:

/* Add this line after the FROM patient p line */
INNER JOIN patplan pp ON p.PatNum = pp.PatNum

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.