Unscheduled Treatment Plan Value by Provider

If you are looking to identify which providers have the highest volume of unbooked work, this open dental unscheduled treatment query provides a clear breakdown of treatment plan values. This report helps your front desk team prioritize follow-up calls to patients who have accepted treatment plans but have not yet scheduled their appointments.

The Query

This query calculates the total dollar value of all procedures currently marked as "Treatment Planned" (ProcStatus = 1) for active patients, grouped by the provider assigned to those procedures.

/* Set the date range for the treatment plan creation date */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-12-31';

SELECT 
    p.Abbr AS Provider,
    COUNT(pl.ProcNum) AS 'Count of Procedures',
    SUM(pl.ProcFee) AS 'Total Value'
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 SUM(pl.ProcFee) 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.

Understanding the Results

• Provider: The abbreviation of the provider assigned to the treatment-planned procedure.
• Count of Procedures: The total number of individual procedures currently sitting in the treatment plan for that provider within the selected date range.
• Total Value: The sum of the ProcFee for all those procedures. This represents the potential production revenue currently "sitting" in your treatment plans.

How to Customize

You can easily adjust this report to fit your specific needs by modifying the lines at the top of the query:

• Change the Date Range: Update the @FromDate and @ToDate values. For example, to see all treatment plans created in the last six months, change the dates accordingly.
• 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) just before the GROUP BY line.

Variations

Filter by Specific Procedure Category

If you only want to see the value of unscheduled restorative work (excluding hygiene), you can join the procedurecode table and filter by ProcCat.

/* Add this join to the query */
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
/* Add this to the WHERE clause */
AND pc.ProcCat = 1 /* Verify your ProcCat ID in Setup > Definitions > Proc Code Categories */

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.