Identify Patients Seen Only Once

If you are looking to improve your practice reactivation efforts, you need to identify patients who visited your office exactly one time and never returned. This open dental one visit patients query helps you isolate those 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, 
    MAX(pl.ProcDate) AS LastVisitDate
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(DISTINCT pl.ProcDate) = 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 assigned to 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.
• LastVisitDate: The date of the single completed procedure found in their record.

How to Customize

You can easily adjust the date range to focus on specific timeframes.

• Change the Date Range: Modify the @StartDate and @EndDate lines at the top of the query. For example, to look at patients seen only once in the year 2024, change the lines to:
  SET @StartDate = '2024-01-01';
SET @EndDate = '2024-12-31';
• Filter by Provider: If you only want to see patients whose single visit was with a specific provider, add this line before the GROUP BY clause:
  AND pl.ProvNum = 1
  (Replace '1' with the actual ProvNum of the provider you wish to filter by).

Variations

If you want to ensure you are only contacting patients who have not been back for a long time, you can add a filter to exclude anyone who has had a scheduled appointment since their initial visit.

-- Variation: Exclude patients with future appointments
SELECT p.PatNum, p.LName, p.FName
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.PatStatus = 0
  AND pl.ProcStatus = 2
  AND pl.ProcDate BETWEEN '2020-01-01' AND '2024-12-31'
GROUP BY p.PatNum
HAVING COUNT(DISTINCT pl.ProcDate) = 1
AND NOT EXISTS (
    SELECT 1 FROM appointment a 
    WHERE a.PatNum = p.PatNum 
    AND a.AptDateTime > NOW()
);

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.