Finding Perio Patients Overdue for SRP

If you are struggling to identify which of your periodontal patients have fallen through the cracks and are now overdue for their scaling and root planing (SRP) or maintenance, this report is for you. This open dental SRP overdue query helps you generate a list of active patients who have previously had SRP procedures completed but have not had a follow-up hygiene visit within your specified timeframe.

The Query

Copy and paste the following code into the User Query window. This query looks for patients who have had an SRP procedure (codes D4341 or D4342) in the past and calculates how long it has been since their last completed procedure.

SET @MonthsOverdue = 6;
SET @StartDate = '2020-01-01';

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone,
    MAX(pl.ProcDate) AS LastSRPDate,
    DATEDIFF(CURDATE(), MAX(pl.ProcDate)) / 30 AS MonthsSinceLastSRP
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
WHERE p.PatStatus = 0
AND pc.ProcCode IN ('D4341', 'D4342')
AND pl.ProcStatus = 2
AND pl.ProcDate >= @StartDate
GROUP BY p.PatNum
HAVING MonthsSinceLastSRP > @MonthsOverdue
ORDER BY MonthsSinceLastSRP 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 the text box. You can click the "Export" button to save these results as a CSV file for Excel.

Understanding the Results

• PatNum: The unique identification number for the patient in your system.
• LName/FName: The patient's last and first name.
• HmPhone/WirelessPhone: Contact information to help your front desk staff reach out for scheduling.
• LastSRPDate: The date the most recent completed SRP procedure was posted to the patient's ledger.
• MonthsSinceLastSRP: The calculated number of months that have passed since that last procedure, helping you prioritize who to call first.

How to Customize

You can easily adjust the parameters at the top of the query to fit your practice's needs:

• Change the timeframe: To change the definition of "overdue," modify the number in the first line: SET @MonthsOverdue = 6;. If you want to see patients overdue by 9 months, change the 6 to 9.
• Change the start date: If you only want to look at patients treated recently, change the date in the second line: SET @StartDate = '2025-01-01';.

Variations

If you want to narrow this list down to only patients who have insurance, you can add an INNER JOIN to the patplan table. This is useful if you want to focus your outreach on patients whose insurance benefits might be resetting soon.

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.