Find Inactive Patients Seen in Last 12 Months

If you are trying to identify patients who were seen in your office within the last year but have since become inactive, this report is for you. Running an open dental inactive patients query helps you pinpoint individuals who may have slipped through the cracks so you can reach out and re-engage them.

The Query

Copy and paste the following code into your User Query window. This query looks for patients currently marked as "Inactive" who had a completed procedure in the last 12 months.

SET @EndDate = CURDATE();
SET @StartDate = DATE_SUB(CURDATE(), INTERVAL 12 MONTH);

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    MAX(pl.ProcDate) AS LastSeenDate
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.PatStatus = 2
  AND pl.ProcStatus = 2
  AND pl.ProcDate BETWEEN @StartDate AND @EndDate
GROUP BY p.PatNum
ORDER BY LastSeenDate DESC;

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 the list as a CSV file for Excel or your marketing software.

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.
• Email: The email address on file for the patient.
• LastSeenDate: The date of the most recent completed procedure found for that patient within the specified 12-month window.

How to Customize

You can easily adjust the timeframe or filter the results without needing to be a database expert.

• Change the Date Range: If you want to look back 18 months instead of 12, change the number in the second line of the query:
  SET @StartDate = DATE_SUB(CURDATE(), INTERVAL 18 MONTH);
• Filter by Provider: If you only want to see inactive patients associated with a specific provider, add this line before the GROUP BY clause (replace 1 with the actual ProvNum from your provider list):
  AND p.PriProv = 1

Variations

If you want to refine your list further, you can adjust the WHERE clause. For example, if you only want to see patients who have a balance, you could add AND p.EstBalance > 0 to the WHERE section. Alternatively, to exclude patients who are deceased or archived, ensure your PatStatus filter remains strictly set to 2 (Inactive) as shown in the main query.

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.