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 a clean breakdown of payments attributed to them. It is the most reliable way to generate an open dental collections report query without manually calculating splits in the Account Module.

The Query

Copy and paste the following code into your User Query window. You can adjust the FromDate and ToDate variables at the top to match the period you want to analyze.

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 the text box.

Understanding the Results

• Provider: This column displays the abbreviation of the provider as set up in your Setup > Definitions > Providers list.
• TotalCollected: This is the sum of all payment splits (paysplit.SplitAmt) assigned to that specific provider within the date range you defined.

How to Customize

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

• Change the Date Range: Edit the dates inside the single quotes on the first two lines. For example, change '2026-01-01' to '2026-02-01' to start your report on February 1st.
• Filter by Clinic: If you have multiple locations and only want to see data for one, add this line right before the GROUP BY line:
  AND ps.ClinicNum = 1
  (Replace '1' with your specific ClinicNum, which you can find in Setup > Clinics).

Variations

If you want to see collections broken down by the specific payment type (e.g., Insurance vs. Patient payments), you can use this variation:

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

SELECT 
    p.Abbr AS Provider,
    pay.PayType,
    SUM(ps.SplitAmt) AS TotalCollected
FROM paysplit ps
INNER JOIN provider p ON ps.ProvNum = p.ProvNum
INNER JOIN payment pay ON ps.PayNum = pay.PayNum
WHERE ps.DatePay >= @FromDate 
AND ps.DatePay <= @ToDate
GROUP BY p.ProvNum, pay.PayType;

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.