Unscheduled Treatment Plan Value by Provider

If you are trying to identify how much potential production is sitting in your Treatment Plan Module, this report is for you. This open dental unscheduled treatment query provides a clear breakdown of the total dollar value of treatment-planned procedures, grouped by the assigned provider, so you can prioritize follow-ups effectively.

The Query

Copy and paste the following code into your User Query window. This query looks for all procedures with a status of "Treatment Planned" (ProcStatus = 1) for active patients.

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

SELECT 
    p.Abbr AS Provider,
    COUNT(pl.ProcNum) AS TotalProcedures,
    SUM(pl.ProcFee) AS TotalValue
FROM procedurelog pl
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
INNER JOIN patient pat ON pl.PatNum = pat.PatNum
WHERE pl.ProcStatus = 1
AND pl.ProcDate >= @FromDate
AND pl.ProcDate <= @ToDate
AND pat.PatStatus = 0
GROUP BY p.ProvNum
ORDER BY TotalValue 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, showing the provider, the count of procedures, and the total dollar value.

Understanding the Results

• Provider: The abbreviation of the provider assigned to the treatment-planned procedure.
• TotalProcedures: The total number of individual procedures currently sitting in the Treatment Plan Module for that provider.
• TotalValue: The sum of the ProcFee for all those procedures. This represents the total potential production value for that provider.

How to Customize

You can easily adjust this report to fit your specific needs by changing the values in the first two lines of the query:

• Change the Date Range: Modify the @FromDate and @ToDate lines to look at a specific quarter or year. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Clinic: If you have multiple locations, you can add a line to the WHERE clause to filter by a specific clinic. Add AND pl.ClinicNum = 1 (replace 1 with your specific Clinic Number) before the GROUP BY line.

Variations

If you want to see which specific patients are contributing to these totals, you can remove the GROUP BY and COUNT functions to list individual patients:

SELECT 
    pat.LName, 
    pat.FName, 
    pl.ProcFee, 
    p.Abbr AS Provider
FROM procedurelog pl
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
INNER JOIN patient pat ON pl.PatNum = pat.PatNum
WHERE pl.ProcStatus = 1
AND pat.PatStatus = 0
ORDER BY pat.LName;

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.