Referral Tracking by Referring Doctor

If you are trying to determine which local dentists or specialists are sending the most new patients to your practice, you need a clear way to pull that data. This open dental referral tracking query allows you to see exactly which referring doctors are responsible for your new patient growth within a specific timeframe.

The Query

Copy and paste the following code into your User Query window. This query filters for active patients and allows you to set the start and end dates for the referral period.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-03-31';

SELECT 
    r.LName AS 'Referral Last Name',
    r.FName AS 'Referral First Name',
    p.LName AS 'Patient Last Name',
    p.FName AS 'Patient First Name',
    ra.RefDate AS 'Referral Date'
FROM referral r
INNER JOIN refattach ra ON r.ReferralNum = ra.ReferralNum
INNER JOIN patient p ON ra.PatNum = p.PatNum
WHERE ra.RefDate BETWEEN @FromDate AND @ToDate
AND ra.IsFrom = 1
AND p.PatStatus = 0
ORDER BY r.LName, r.FName, ra.RefDate;

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

• Referral Last Name / First Name: The name of the doctor or individual who referred the patient to your office.
• Patient Last Name / First Name: The name of the patient who was referred.
• Referral Date: The date recorded in the patient's file for when the referral was received or attached.

How to Customize

You can easily adjust the timeframe of the report by changing the dates in the first two lines of the query.

• Change the Date Range: Modify the @FromDate and @ToDate values. For example, to see referrals for the entire year of 2025, change the lines to:
  SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-31';

• Filter by Specific Referral: If you only want to see referrals from one specific doctor, add this line before the ORDER BY clause:
  AND r.LName = 'Smith' (Replace 'Smith' with the actual last name of the doctor).

Variations

If you want to see a summary count of how many patients each doctor has referred rather than a list of individual patients, you can use this variation:

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-03-31';

SELECT 
    r.LName, 
    r.FName, 
    COUNT(ra.PatNum) AS 'Total Referrals'
FROM referral r
INNER JOIN refattach ra ON r.ReferralNum = ra.ReferralNum
WHERE ra.RefDate BETWEEN @FromDate AND @ToDate
AND ra.IsFrom = 1
GROUP BY r.ReferralNum
ORDER BY COUNT(ra.PatNum) DESC;

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.