Referral Tracking by Referring Doctor

If you are trying to determine which local dentists or specialists are sending the most 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. You can adjust the FromDate and ToDate variables at the top to change the reporting period.

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

SELECT 
    r.LName AS 'Ref Last Name',
    r.FName AS 'Ref First Name',
    COUNT(ra.PatNum) AS 'Total Referred Patients'
FROM referral r
INNER JOIN refattach ra ON r.ReferralNum = ra.ReferralNum
INNER JOIN patient p ON ra.PatNum = p.PatNum
WHERE ra.RefDate >= @FromDate 
AND ra.RefDate <= @ToDate
AND ra.IsFrom = 1
AND p.PatStatus = 0
GROUP BY r.ReferralNum
ORDER BY COUNT(ra.PatNum) DESC;

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

• Ref Last Name / Ref First Name: This identifies the doctor or referral source as entered in your Manage Module under the Referrals list.
• Total Referred Patients: This is the count of unique patients who have a referral attachment linked to this specific doctor within the date range you selected.

How to Customize

You can easily modify this query to fit your needs:

• Change the Date Range: Modify the dates in the first two lines. For example, to look at the entire year of 2025, change the lines to:
  SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-31';
• Filter by Specific Referral Type: If you only want to see referrals from other doctors (and exclude patient-to-patient referrals), add this line before the GROUP BY clause:
  AND r.IsDoctor = 1

Variations

If you want to see the specific names of the patients referred by these doctors rather than just a total count, you can use this variation:

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

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

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.