Collections by Provider Report

If you need to see exactly how much money each provider is bringing into the practice over a specific period, this query provides the answer. By using this open dental collections report query, you can quickly analyze provider performance without manually calculating splits or digging through individual patient accounts.

The Query

This query calculates the total payments allocated to each provider within your chosen date range.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-01-31';

SELECT 
    p.Abbr AS Provider,
    SUM(ps.SplitAmt) AS TotalCollected
FROM paysplit ps
INNER JOIN provider p ON ps.ProvNum = p.ProvNum
WHERE ps.DatePay >= @FromDate 
AND ps.DatePay <= @ToDate
GROUP BY p.ProvNum
ORDER BY TotalCollected 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 provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• Provider: This column displays the provider abbreviation as set up in your Manage Module > Providers.
• TotalCollected: This is the sum of all payment splits (paysplit) assigned to that specific provider within the date range you defined. Note that this reflects the money allocated to the provider, not necessarily the total production.

How to Customize

You can easily adjust this report to fit your specific needs by changing the values at the top of the query:

• Change the Date Range: Modify the dates inside the single quotes for @FromDate and @ToDate. Ensure you keep the YYYY-MM-DD format.
• Filter by Clinic: If you have multiple locations and only want to see data for one, add a line to the WHERE clause. For example, add AND ps.ClinicNum = 1 (replace 1 with your specific Clinic Number found in Setup > Account > Clinics).

Variations

If you want to see production instead of collections, you can use a similar structure targeting the procedurelog table:

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-01-31';

SELECT 
    p.Abbr AS Provider,
    SUM(pl.ProcFee) AS TotalProduction
FROM procedurelog pl
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
WHERE pl.ProcDate >= @FromDate 
AND pl.ProcDate <= @ToDate
AND pl.ProcStatus = 2 -- Only complete procedures
GROUP BY p.ProvNum;

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.