New Patient Referral Report Query

If you are struggling to identify which marketing efforts or word-of-mouth sources are actually bringing new patients into your practice, this query provides the clarity you need. By running this open dental new patient referral report, you can instantly see a list of new patients alongside their specific referral source for any given month.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET dates at the top to match the specific time period you wish to analyze.

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

SELECT 
    p.LName, 
    p.FName, 
    p.DateFirstVisit, 
    r.LName AS ReferralLName, 
    r.FName AS ReferralFName,
    ref.RefType
FROM patient p
LEFT JOIN refattach ref ON p.PatNum = ref.PatNum AND ref.IsFrom = 1
LEFT JOIN referral r ON ref.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 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.
• RefType: This indicates the category of the referral (e.g., Patient, Doctor, or Marketing source) as defined in your Referral setup.

How to Customize

You can easily modify this report to fit your specific needs by changing the values in the SET lines at the top of the query:

• Change the Date Range: Update the @FromDate and @ToDate values. For example, to see data for February 2026, change the lines to:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';
• Filter by Specific Referral Type: If you only want to see patients referred by other patients, add this line before the ORDER BY clause:
  AND ref.RefType = 'Patient'

Variations

If you want to see only patients who have not been assigned a referral source yet, you can use this variation to help your front desk staff clean up missing data:

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

SELECT LName, FName, DateFirstVisit
FROM patient
WHERE DateFirstVisit >= @FromDate 
AND DateFirstVisit <= @ToDate
AND PatStatus = 0
AND PatNum NOT IN (SELECT PatNum FROM refattach WHERE IsFrom = 1);

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.