Open Dental Unearned Income Query

If you are struggling to reconcile your patient accounts because of prepayments or credits, this open dental unearned income query will help you identify exactly which patients have unearned income sitting on their accounts. This report is essential for office managers who need to maintain accurate financial records and ensure that patient credits are applied correctly to future treatment.

The Query

You can copy and paste the following code directly into your User Query window. This query calculates the total unearned income by summing the SplitAmt from the paysplit table where the UnearnedType is greater than zero.

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

SELECT 
    p.LName, 
    p.FName, 
    p.PatNum, 
    SUM(ps.SplitAmt) AS TotalUnearned,
    ps.DatePay
FROM paysplit ps
INNER JOIN patient p ON ps.PatNum = p.PatNum
WHERE ps.UnearnedType > 0
AND ps.DatePay BETWEEN @FromDate AND @ToDate
AND p.PatStatus = 0
GROUP BY ps.PatNum
ORDER BY p.LName, p.FName;

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 you the patient names and their corresponding unearned income totals.

Understanding the Results

• LName/FName: The patient's last and first name.
• PatNum: The unique identification number for the patient in your database.
• TotalUnearned: The sum of all payments marked as unearned for that specific patient within the date range you selected.
• DatePay: The date the payment was originally entered into the system.

How to Customize

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

• Change the Date Range: Modify the @FromDate and @ToDate lines at the top of the query. For example, to see all unearned income for the entire year of 2025, change the lines to:
  SET @FromDate = '2025-01-01';
SET @ToDate = '2025-12-31';
• Filter by Clinic: If you have multiple locations, you can add a filter to see only one clinic. Add this line before the GROUP BY clause:
  AND ps.ClinicNum = 1
  (Replace '1' with your specific ClinicNum found in Setup > Clinics).

Variations

If you want to see only patients who have a significant amount of unearned income, you can add a HAVING clause to the end of the query. For example, to only see patients with more than $500 in unearned income, add this line before the ORDER BY clause:

HAVING SUM(ps.SplitAmt) > 500

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.