New Patient Referral Report Query

If you are struggling to identify which marketing efforts or referral sources are actually bringing new patients into your practice, this query provides the clarity you need. By pulling a list of new patients alongside their attached referral source, you can easily see which channels are driving your growth.

The Query

/* Set your date range here */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-03-31';

SELECT 
    p.LName, 
    p.FName, 
    p.DateFirstVisit, 
    r.LName AS ReferralLName, 
    r.FName AS ReferralFName,
    ra.RefType
FROM patient p
LEFT JOIN refattach ra ON p.PatNum = ra.PatNum AND ra.IsFrom = 1
LEFT JOIN referral r ON ra.ReferralNum = r.ReferralNum
WHERE p.DateFirstVisit >= @FromDate 
AND p.DateFirstVisit <= @ToDate
AND p.PatStatus = 0
ORDER BY p.DateFirstVisit;

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 last and first name of the new patient.
• DateFirstVisit: The date recorded in the patient's file as their first visit to your practice.
• ReferralLName/ReferralFName: The name of the person or entity that referred the patient to you. If this is blank, no referral source was attached to the patient's file.
• RefType: This indicates the nature of the referral (e.g., a patient, a doctor, or a marketing source) as defined in your referral setup.

How to Customize

You can easily adjust the timeframe of your report by changing the dates in the SET lines at the very top of the query.

• Change Date Range: Modify the @FromDate and @ToDate values. For example, to see data for the month of February 2026, change the lines to:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';

• Filter by Provider: If you only want to see new patients assigned to a specific provider, add this line before the ORDER BY clause:
  AND p.PriProv = 1 (Replace 1 with the actual ProvNum of your provider).

Variations

If you want to see only patients who were referred by other patients (to track your internal referral program), you can add a filter to the end of the query:

-- Add this before the ORDER BY clause
AND r.IsDoctor = 0

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.