Case Acceptance Rate by Provider

If you need to identify which providers are successfully converting treatment-planned procedures into completed work, this query provides a clear breakdown of your practice's performance. By comparing the total dollar value of treatment-planned procedures against those that were actually completed, you can easily calculate your open dental case acceptance rate query results for each provider.

The Query

Copy and paste the following code into your User Query window.

SET @StartDate = '2026-01-01';
SET @EndDate = '2026-03-31';

SELECT 
    p.Abbr AS Provider,
    SUM(CASE WHEN pl.ProcStatus = 1 THEN pl.ProcFee ELSE 0 END) AS 'Total_TP_Amount',
    SUM(CASE WHEN pl.ProcStatus = 2 THEN pl.ProcFee ELSE 0 END) AS 'Total_Completed_Amount',
    ROUND(
        (SUM(CASE WHEN pl.ProcStatus = 2 THEN pl.ProcFee ELSE 0 END) / 
        NULLIF(SUM(CASE WHEN pl.ProcStatus = 1 THEN pl.ProcFee ELSE 0 END) + 
               SUM(CASE WHEN pl.ProcStatus = 2 THEN pl.ProcFee ELSE 0 END), 0)) * 100, 2
    ) AS 'Acceptance_Rate_Percent'
FROM procedurelog pl
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
WHERE pl.ProcDate BETWEEN @StartDate AND @EndDate
AND pl.ProcStatus IN (1, 2)
GROUP BY p.ProvNum
ORDER BY Acceptance_Rate_Percent 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 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 procedure.
• Total_TP_Amount: The sum of all procedures currently marked as "Treatment Planned" (Status 1) within your chosen date range.
• Total_Completed_Amount: The sum of all procedures marked as "Complete" (Status 2) within your chosen date range.
• Acceptance_Rate_Percent: A calculated percentage representing the ratio of completed work compared to the total volume of planned and completed work combined.

How to Customize

You can easily adjust the timeframe or focus of this report by editing the first two lines of the query:

• Change the Date Range: Modify the dates inside the single quotes for @StartDate and @EndDate. Ensure you keep the YYYY-MM-DD format.
• Filter by Clinic: If you have multiple locations and only want to see one, add a line before the GROUP BY clause: AND pl.ClinicNum = 1 (replace 1 with your specific ClinicNum).

Variations

If you want to focus specifically on high-value procedures, you can add a filter to exclude low-cost items like simple exams or radiographs. Add this line before the GROUP BY clause:

AND pl.ProcFee > 100

This will only include procedures with a fee greater than $100, giving you a better look at "major" case acceptance rather than routine hygiene maintenance.

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.