Year-over-Year Production Query

If you need to see exactly how your practice's monthly production compares to the same period from the previous year, this query provides a clean, side-by-side view. It is perfect for practice owners and office managers who want to track growth trends without manually running multiple standard reports.

The Query

Copy and paste the following code into your User Query window. This query calculates the total completed production for two specific years.

/* Year-over-Year Production Comparison */
SET @Year1 = '2024';
SET @Year2 = '2025';

SELECT 
    MONTHNAME(ProcDate) AS 'Month',
    SUM(CASE WHEN YEAR(ProcDate) = @Year1 THEN ProcFee * UnitQty ELSE 0 END) AS 'Year1_Prod',
    SUM(CASE WHEN YEAR(ProcDate) = @Year2 THEN ProcFee * UnitQty ELSE 0 END) AS 'Year2_Prod'
FROM procedurelog
WHERE ProcStatus = 2
AND YEAR(ProcDate) IN (@Year1, @Year2)
GROUP BY MONTH(ProcDate)
ORDER BY MONTH(ProcDate);

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

• Month: The name of the month (January, February, etc.).
• Year1_Prod: The total gross production for that month in the first year you defined.
• Year2_Prod: The total gross production for that month in the second year you defined.

How to Customize

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

• Change the Years: Update the years inside the single quotes for @Year1 and @Year2. For example, to compare 2023 and 2025, change the lines to:
  SET @Year1 = '2023';
SET @Year2 = '2025';
• Filter by Provider: If you want to see production for a specific provider, add this line before the GROUP BY clause:
  AND ProvNum = 1 (Replace 1 with the actual ProvNum from your Provider list).
• Filter by Clinic: If you have multiple locations, add this line before the GROUP BY clause:
  AND ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to see Net Production (which subtracts insurance write-offs) instead of Gross Production, you would need to join the claimproc table to subtract the WriteOff amounts. Because this requires more complex joins, we recommend using the standard Production and Income report in the Reports > Standard menu for net figures, as it handles these calculations automatically.

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.