Monthly Production vs Collections Query

If you need to see exactly how much your practice produced versus how much you collected in a specific month, this report provides the data you need without manual calculation. This open dental production vs collections query gives you a clear snapshot of your monthly financial performance.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET variables at the top to define the date range you want to analyze.

/* Set your date range here */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-01-31';

SELECT 
    'Production' AS Category, 
    SUM(ProcFee * UnitQty) AS Total
FROM procedurelog
WHERE ProcStatus = 2 
AND ProcDate BETWEEN @FromDate AND @ToDate

UNION ALL

SELECT 
    'Collections' AS Category, 
    SUM(SplitAmt) AS Total
FROM paysplit
WHERE DatePay BETWEEN @FromDate AND @ToDate;

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

• Category: This column labels the row as either "Production" or "Collections."
• Total: This column shows the dollar amount for that category.
  • Production: This calculates the sum of all completed procedures (ProcStatus = 2) within your chosen date range, multiplied by the quantity of units.
  • Collections: This calculates the sum of all payment splits (paysplit) recorded within your chosen date range. This is the most accurate way to track collections, as it accounts for payments applied to specific procedures or accounts.

How to Customize

You can easily modify this query to fit your specific reporting needs:

• Change the Date Range: Modify the dates in the SET statements at the top. For example, to look at February 2026, change the lines to:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';
• 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 using the ProvNum. You can find the ProvNum by going to Setup > Providers.
  • Example addition: AND ProvNum = 1 (Add this line before the UNION ALL and again before the final semicolon).

Variations

If you want to see how much of your collections came from insurance versus patient payments, you can use this variation to break down the paysplit table:

SELECT 
    CASE WHEN p.PayType = 1 THEN 'Patient' ELSE 'Insurance' END AS PaySource,
    SUM(ps.SplitAmt) AS Total
FROM paysplit ps
JOIN payment p ON ps.PayNum = p.PayNum
WHERE ps.DatePay BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY PaySource;

(Note: Ensure your PayType IDs match your specific setup in Setup > Definitions > Payment Types.)

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.