Average Production per New Patient Query

If you are trying to determine if your marketing efforts are actually paying off, you need to know exactly how much revenue your new patients generate. This open dental new patient value query calculates the average production per new patient over a specific timeframe, helping you make data-driven decisions about your practice growth.

The Query

Copy and paste the following code into your User Query window. This query identifies patients marked as "New" in their first appointment and sums their completed production within their first 90 days.

SET @StartDate = '2025-01-01';
SET @EndDate = '2025-12-31';

SELECT 
    COUNT(DISTINCT p.PatNum) AS 'NewPatientCount',
    SUM(pl.ProcFee) AS 'TotalProduction',
    ROUND(SUM(pl.ProcFee) / COUNT(DISTINCT p.PatNum), 2) AS 'AvgProdPerNewPatient'
FROM patient p
INNER JOIN appointment a ON p.PatNum = a.PatNum
INNER JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE a.IsNewPatient = 1
AND a.AptStatus = 2
AND pl.ProcStatus = 2
AND pl.ProcDate >= p.DateFirstVisit
AND pl.ProcDate <= DATE_ADD(p.DateFirstVisit, INTERVAL 90 DAY)
AND p.DateFirstVisit BETWEEN @StartDate AND @EndDate
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: The total number of unique patients who had an appointment marked as "New Patient" within your selected date range.
• TotalProduction: The sum of all completed procedure fees (ProcStatus = 2) for these specific patients during their first 90 days in your practice.
• AvgProdPerNewPatient: The mathematical average (Total Production divided by New Patient Count). This is the key number to track for your marketing ROI.

How to Customize

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

• Change the Date Range: Modify the two lines at the top of the query. Change '2025-01-01' and '2025-12-31' to your desired start and end dates.
• Change the Time Window: If you want to see production for the first 6 months instead of 90 days, change INTERVAL 90 DAY to INTERVAL 6 MONTH.
• Filter by Provider: If you want to see this data for a specific provider, add this line to the WHERE clause: AND pl.ProvNum = 1 (replace 1 with the actual ProvNum from your provider list).

Variations

If you want to see how much of this production is coming from hygiene versus restorative work, you can run a variation that filters by procedurecode.ProcCat. By adding a join to the procedurecode table, you can isolate specific categories of work to see which new patients are accepting comprehensive treatment plans versus those only coming in for cleanings.

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.