Average Production per New Patient Query

If you are trying to determine the financial impact of your marketing efforts or simply want to know the average value of a new patient to your practice, this report is essential. This open dental new patient value query calculates the total production for patients who had their first visit within a specific date range, allowing you to see exactly what those new patients are worth to your bottom line.

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 define the period in which the patients had their first visit.

SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-31';

SELECT 
    COUNT(p.PatNum) AS 'NewPatientCount',
    SUM(pl.ProcFee) AS 'TotalProduction',
    ROUND(SUM(pl.ProcFee) / COUNT(p.PatNum), 2) AS 'AvgProdPerNewPatient'
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus = 2
AND pl.ProcDate >= p.DateFirstVisit
AND p.PatStatus = 0;

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 the text box.

Understanding the Results

• NewPatientCount: This is the total number of unique patients who had a DateFirstVisit recorded within the date range you specified in the SET statements.
• TotalProduction: This is the sum of all completed procedure fees (ProcStatus = 2) for those specific patients, starting from their first visit date.
• AvgProdPerNewPatient: This is the mathematical average, calculated by dividing the TotalProduction by the NewPatientCount.

How to Customize

You can easily modify this query to fit your specific reporting needs:

• Change the Date Range: Update the dates in the SET statements at the top of the query. Ensure they remain in the 'YYYY-MM-DD' format.
• Filter by Provider: If you want to see the value of new patients for a specific provider, add this line to the WHERE clause:
  AND pl.ProvNum = 1 (Replace 1 with the actual ProvNum from your Provider setup).
• Filter by Clinic: If you have multiple locations, you can filter by clinic by adding this line to the WHERE clause:
  AND p.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to see the average production specifically for new patients who have insurance, you can add a join to the patplan table:

-- Add this join to the query above
INNER JOIN patplan pp ON p.PatNum = pp.PatNum
-- This will filter the results to only include patients with at least one active insurance plan.

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.