Open Dental Unearned Income Query

If you are struggling to reconcile your patient prepayments or need a clear list of funds sitting in your system that haven't been applied to procedures, this report is for you. This open dental unearned income query provides a clean, exportable list of all unearned income currently held in your database.

The Query

Copy and paste the following code into your User Query window. This query filters for active patients and calculates the remaining unearned balance for each entry.

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

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

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. You can click Export to save the data as a CSV file for Excel.

Understanding the Results

• LName / FName: The patient's last and first name.
• DatePay: The date the payment was originally entered into the system.
• OriginalAmount: The total amount of the payment split that was marked as unearned at the time of entry.
• UnearnedType: This corresponds to the "Unearned Type" definition you have set up in your practice (e.g., Prepayment, Insurance Overpayment).
• RemainingUnearned: This shows the current balance of that specific unearned split. If this matches the OriginalAmount, the entire payment is still unapplied.

How to Customize

You can easily adjust the parameters of this report to fit your needs:

• Change the Date Range: Modify the SET @FromDate and SET @ToDate lines at the top of the query. Ensure you keep the format as 'YYYY-MM-DD'.
• Filter by Clinic: If you have multiple locations, add this line right before the ORDER BY clause:
  AND ps.ClinicNum = 1
  (Replace '1' with your specific ClinicNum found in Setup > Clinics).
• Filter by Provider: To see unearned income associated with a specific provider, add this line:
  AND ps.ProvNum = 12
  (Replace '12' with the ProvNum of the provider).

Variations

If you want to see only prepayments that are older than 90 days to help with your accounting cleanup, you can add a filter to the WHERE clause:

AND ps.DatePay < DATE_SUB(CURDATE(), INTERVAL 90 DAY)

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.