Finding Over Due Xrays in Open Dental

If you are struggling to keep track of which patients are missing their diagnostic imaging, this query identifies patients who have not had a bitewing or full-mouth series procedure completed within a specific timeframe. This report helps your front desk staff proactively reach out to patients with over due xrays to get them back on the schedule.

The Query

This query looks for patients with an active status who have not had a procedure code matching "D027" (bitewings) or "D0330" (panoramic) within the last 24 months.

/* Set the number of months to look back for x-rays */
SET @MonthsBack = 24;

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 pc.ProcCode IN ('D0270', 'D0272', 'D0274', 'D0330')
AND pl.ProcStatus = 2 /* Complete */
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.

Understanding the Results

• LName / FName: The patient's last and first name.
• HmPhone / WirelessPhone: Contact numbers to assist your team in calling the patient.
• Email: Useful if your office prefers to send recall reminders via email.
• LastXrayDate: The date of the most recent bitewing or panoramic procedure found in the patient's record. If this date is older than your threshold, they appear on this list.

How to Customize

You can easily adjust the sensitivity of this report by changing the variables at the top of the query:

• Change the Timeframe: To look for patients who haven't had x-rays in 18 months instead of 24, change the first line to:
  SET @MonthsBack = 18;
• Filter by Procedure Code: If you only want to track bitewings, remove the 'D0330' from the IN clause:
  AND pc.ProcCode IN ('D0270', 'D0272', 'D0274')

Variations

If you want to focus only on patients who have insurance, you can add a join to the patplan table. This helps prioritize patients who are likely to have coverage for their diagnostic visits.

/* Variation: Only patients with active insurance */
SELECT 
    p.LName, 
    p.FName, 
    p.HmPhone
FROM patient p
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
/* ... rest of the query logic remains the same ... */

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.