Open Dental Overdue Recall Query

If you are struggling to keep your hygiene schedule full, you need a reliable way to identify patients who have fallen through the cracks. This open dental overdue recall query allows you to instantly pull a list of active patients who are past their due date for a hygiene visit, helping your front desk team prioritize their outreach efforts.

The Query

Copy and paste the following code into your User Query window. This query looks for active patients whose recall date is earlier than the date you specify.

/* Set the date to find patients overdue as of this date */
SET @AsOfDate = '2026-03-27';

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    r.DateDue,
    DATEDIFF(@AsOfDate, r.DateDue) AS DaysOverdue
FROM patient p
INNER JOIN recall r ON p.PatNum = r.PatNum
WHERE p.PatStatus = 0 
  AND r.DateDue < @AsOfDate
  AND r.DateDue > '2000-01-01'
ORDER BY r.DateDue 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.

Understanding the Results

• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: Contact numbers to help your team reach out quickly.
• Email: Useful if your office prefers email communication for recall reminders.
• DateDue: The date the patient was officially due for their hygiene appointment according to the Recall module.
• DaysOverdue: A calculated column showing exactly how many days have passed since the patient's due date, helping you prioritize who to call first.

How to Customize

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

• Change the Date: To see who was overdue as of a different date, change the line SET @AsOfDate = '2026-03-27'; to your desired date in YYYY-MM-DD format.
• Filter by Provider: If you want to see overdue patients for a specific hygienist, add this line before the ORDER BY clause: AND p.PriProv = 1 (replace 1 with the actual ProvNum of the provider).
• Filter by Clinic: If you have multiple locations, add AND p.ClinicNum = 1 (replace 1 with your specific ClinicNum) to the WHERE clause.

Variations

Only Patients Overdue by More Than 90 Days

If you want to focus on long-term overdue patients, add a HAVING clause to the end of the query:

...
ORDER BY r.DateDue ASC
HAVING DaysOverdue > 90;

Include Only Patients with Insurance

To prioritize patients who have active insurance benefits, add an INNER JOIN to the patplan table:

SELECT p.LName, p.FName, p.HmPhone, r.DateDue
FROM patient p
INNER JOIN recall r ON p.PatNum = r.PatNum
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
WHERE p.PatStatus = 0 AND r.DateDue < @AsOfDate
GROUP BY p.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.