Find Patients Seen Only Once in Open Dental

If you are looking to improve your patient retention, identifying patients who visited your practice exactly one time is a great place to start. This open dental one visit patients query helps you generate a list of these individuals so you can reach out and invite them back for a follow-up appointment.

The Query

This query identifies active patients who have exactly one completed procedure in their history.

SET @StartDate = '2020-01-01';
SET @EndDate = '2025-12-31';

SELECT 
    p.PatNum, 
    p.LName, 
    p.FName, 
    p.WirelessPhone, 
    p.Email, 
    COUNT(pl.ProcNum) AS TotalProcedures
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.PatStatus = 0
  AND pl.ProcStatus = 2
  AND pl.ProcDate BETWEEN @StartDate AND @EndDate
GROUP BY p.PatNum
HAVING COUNT(pl.ProcNum) = 1;

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

• PatNum: The unique identification number for the patient in your system.
• LName / FName: The patient's last and first name.
• WirelessPhone / Email: Contact information to help your front desk staff reach out for reactivation.
• TotalProcedures: This will show as "1" for all results, confirming these patients have only had one completed procedure recorded in the specified date range.

How to Customize

You can easily adjust the parameters of this report to better fit your needs:

• Change the Date Range: Modify the dates in the first two lines of the query. For example, to look at patients seen in 2024 only, change the lines to:
  SET @StartDate = '2024-01-01';
SET @EndDate = '2024-12-31';
• Filter by Provider: If you want to see patients who only saw a specific provider, add a line to the WHERE clause:
  AND pl.ProvNum = 1 (Replace "1" with the actual ProvNum of the provider).
• Filter by Clinic: If you have multiple locations, you can filter by clinic by adding this to the WHERE clause:
  AND pl.ClinicNum = 1 (Replace "1" with your specific ClinicNum).

Variations

If you want to refine this list further, you can add filters to the query:

• Exclude Patients with Future Appointments: To ensure you aren't calling patients who already have a visit scheduled, you can add a NOT EXISTS clause to exclude anyone with an appointment status of "Scheduled" (AptStatus = 1).
• Filter by Procedure Code: If you only want to find patients who came in for a specific service (like a new patient exam), you can add AND pl.CodeNum = [YourCodeNum] to the WHERE clause.

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.