Monthly Production vs Collections Query

If you need to quickly compare your monthly revenue against your actual cash intake to gauge practice health, this report provides the numbers you need. This open dental production vs collections query pulls your total completed production and total payments received for a specific date range directly from your database.

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 the month you are analyzing.

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

SELECT 
    'Production' AS Category,
    SUM(ProcFee * UnitQty) AS TotalAmount
FROM procedurelog
WHERE ProcStatus = 2
AND DateComplete BETWEEN @StartDate AND @EndDate

UNION ALL

SELECT 
    'Collections' AS Category,
    SUM(SplitAmt) AS TotalAmount
FROM paysplit
WHERE DatePay BETWEEN @StartDate AND @EndDate;

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, showing your total production and total collections for the selected dates.

Understanding the Results

• Category: This column labels the row as either 'Production' or 'Collections'.
• TotalAmount: This column displays the dollar value for that category.
  • Production: This is the sum of all procedures marked as 'Complete' (ProcStatus = 2) within your chosen date range, calculated by multiplying the procedure fee by the quantity.
  • Collections: This is the sum of all payment splits recorded in the system for that date range. This includes patient payments, insurance payments, and any adjustments that were split to a payment.

How to Customize

You can easily modify this report to look at different timeframes or specific providers.

• Change the Date Range: Update the dates in the SET statements at the top of the query. Ensure you keep the format 'YYYY-MM-DD'.
• Filter by Provider: If you want to see production and collections for a specific provider, add a WHERE clause to both parts of the query. For example, add AND ProvNum = 1 (replace '1' with the actual Provider Number from your Provider setup) before the UNION ALL and at the end of the query.

Variations

If you want to see how much of your production was written off due to insurance adjustments, you can add a third section to the query:

UNION ALL

SELECT 
    'Write-Offs' AS Category,
    SUM(WriteOff) AS TotalAmount
FROM claimproc
WHERE DateCP BETWEEN @StartDate AND @EndDate
AND Status = 1; -- Only includes received insurance claims

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.