Crown and Bridge Production Query

If you need to see exactly how much crown and bridge work each provider in your office has completed over a specific time period, this report provides the breakdown you need. This open dental crown production query filters for completed procedures categorized as crowns or bridges, allowing you to track provider performance accurately.

The Query

Copy and paste the following code into your User Query window. You can adjust the StartDate and EndDate variables at the top to fit your reporting needs.

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

SELECT 
    p.Abbr AS Provider,
    pc.ProcCode,
    pc.Descript AS Description,
    COUNT(pl.ProcNum) AS Count,
    SUM(pl.ProcFee) AS TotalProduction
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
WHERE pl.ProcDate BETWEEN @StartDate AND @EndDate
AND pl.ProcStatus = 2
AND (pc.ProcCode LIKE 'D2%' OR pc.ProcCode LIKE 'D6%')
GROUP BY p.ProvNum, pc.ProcCode
ORDER BY Provider, TotalProduction 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 into the text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• Provider: The abbreviation of the provider who performed the work.
• ProcCode: The specific ADA procedure code (e.g., D2740).
• Description: The description of the procedure as defined in your procedure code list.
• Count: The total number of times that specific procedure was completed by that provider within your date range.
• TotalProduction: The sum of the fees for those procedures.

How to Customize

• Change the Date Range: Modify the dates in the first two lines of the query. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Clinic: If you have multiple locations and only want to see one, add this line before the GROUP BY clause:
  AND pl.ClinicNum = 1 (Replace 1 with your specific Clinic Number).
• Filter by Provider: To see data for only one provider, add this line before the GROUP BY clause:
  AND p.Abbr = 'DR1' (Replace DR1 with the provider's actual abbreviation).

Variations

If you want to see all crown and bridge work regardless of the specific code, you can simplify the WHERE clause. Alternatively, if you want to include "Treatment Planned" procedures to see potential future production, change pl.ProcStatus = 2 (Complete) to pl.ProcStatus = 1 (Treatment Planned).

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.