How to Run Query for Overdue X-Rays in Open Dental

If you are struggling to identify which patients are past due for their radiographic exams, this report will help you clean up your recall list. Knowing exactly how to run query for overdue xrays allows you to proactively reach out to patients and ensure your hygiene schedule remains full and compliant.

The Query

This query identifies active patients who have a completed procedure code matching common X-ray codes (like bitewings or FMX) that was completed more than 18 months ago.

/* Set the number of months to look back */
SET @MonthsBack = 18;

SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone, 
    p.WirelessPhone, 
    p.Email, 
    MAX(pl.ProcDate) AS LastXrayDate
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 pl.ProcStatus = 2 /* Complete */
  /* Filter for common X-ray codes (D0274, D0210, etc.) */
  AND (pc.ProcCode LIKE 'D027%' OR pc.ProcCode LIKE 'D0210')
GROUP BY p.PatNum
HAVING LastXrayDate < DATE_SUB(CURDATE(), INTERVAL @MonthsBack MONTH)
ORDER BY LastXrayDate 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, showing you the patient name, contact information, and the date of their last recorded X-ray.

Understanding the Results

• LName / FName: The patient's legal name as entered in the Family Module.
• HmPhone / WirelessPhone: Contact numbers to help your front desk staff reach out for scheduling.
• Email: Useful if your office uses automated email reminders for overdue hygiene.
• LastXrayDate: The most recent date a procedure code matching your criteria was marked as "Complete" in the patient's chart.

How to Customize

You can easily adjust this report to fit your office's specific recall protocols:

• Change the Timeframe: Locate the line SET @MonthsBack = 18;. Change the 18 to 12 if you want to see patients overdue by one year instead of 18 months.
• Filter by Provider: If you only want to see patients for a specific hygienist, add this line under the WHERE clause: AND p.PriProv = [ProviderNumber]. (Replace [ProviderNumber] with the actual ID number of the provider).
• Filter by Clinic: If you have multiple locations, add AND p.ClinicNum = [ClinicNumber] to the WHERE clause to isolate results for a single office.

Variations

If you want to see patients who have never had an X-ray recorded in your system, you can use a LEFT JOIN approach to find patients where no procedure log entry exists for those specific codes. Additionally, you can filter by BillingType if you only want to target patients with specific insurance plans or membership programs.

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.