Year-over-Year Production Query

If you need to compare your practice's production between two different years to identify growth trends, this query provides a clear, side-by-side breakdown. It is designed for office managers and practice owners who need to see total completed production for specific date ranges without manually running multiple standard reports.

The Query

Copy and paste the following code into the User Query window. You can adjust the date variables at the top to match the specific years you wish to compare.

/* Year-over-Year Production Comparison */
/* Set your date ranges below */
SET @Year1_Start = '2024-01-01';
SET @Year1_End = '2024-12-31';
SET @Year2_Start = '2025-01-01';
SET @Year2_End = '2025-12-31';

SELECT 
    'Year 1' AS Period,
    SUM(ProcFee * UnitQty) AS TotalProduction
FROM procedurelog
WHERE ProcStatus = 2 
AND ProcDate BETWEEN @Year1_Start AND @Year1_End

UNION ALL

SELECT 
    'Year 2' AS Period,
    SUM(ProcFee * UnitQty) AS TotalProduction
FROM procedurelog
WHERE ProcStatus = 2 
AND ProcDate BETWEEN @Year2_Start AND @Year2_End;

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

• Period: This column labels the row as either "Year 1" or "Year 2" based on the date ranges you defined in the query.
• TotalProduction: This is the sum of all completed procedures (ProcStatus = 2) within the specified date range. It accounts for the procedure fee multiplied by the quantity of units performed.

How to Customize

You can easily modify this query to look at different timeframes or filter by specific criteria.

• Changing Dates: Simply edit the dates inside the single quotes for the four SET variables at the top of the query. Ensure you keep the YYYY-MM-DD format.
• Filtering by Provider: To limit the results to a specific provider, add an AND clause to both WHERE sections. For example: AND ProvNum = 1. (Replace 1 with the actual ProvNum from your provider setup).
• Filtering by Clinic: If you have multiple locations, you can filter by clinic by adding AND ClinicNum = 1 to both WHERE sections. (Replace 1 with your specific ClinicNum).

Variations

If you want to see a month-by-month breakdown instead of a total annual sum, you can use a variation that groups the data by month:

/* Monthly Production Comparison */
SELECT 
    MONTH(ProcDate) AS MonthNum,
    SUM(CASE WHEN ProcDate BETWEEN '2024-01-01' AND '2024-12-31' THEN ProcFee * UnitQty ELSE 0 END) AS '2024_Prod',
    SUM(CASE WHEN ProcDate BETWEEN '2025-01-01' AND '2025-12-31' THEN ProcFee * UnitQty ELSE 0 END) AS '2025_Prod'
FROM procedurelog
WHERE ProcStatus = 2
GROUP BY MONTH(ProcDate)
ORDER BY MonthNum;

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.