Daily Deposit Report by Payment Type

If you need to reconcile your daily bank deposits or track how much revenue is coming in via cash, check, or credit card, this report provides the exact breakdown you need. By using this open dental daily deposit query, you can quickly see a summary of all payments received within a specific date range, categorized by the payment type defined in your system.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET lines at the top to change the date range for your report.

SET @FromDate = '2026-04-01';
SET @ToDate = '2026-04-02';

SELECT 
    payment.PayDate,
    payment.PayType,
    SUM(payment.PayAmt) AS TotalCollected
FROM payment
WHERE payment.PayDate BETWEEN @FromDate AND @ToDate
GROUP BY payment.PayDate, payment.PayType
ORDER BY payment.PayDate, payment.PayType;

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 total amount collected per payment type for the dates you selected.

Understanding the Results

• PayDate: The date the payment was entered into the system.
• PayType: This corresponds to the definitions you have set up in Setup > Definitions > Payment Types. It shows whether the money was recorded as Cash, Check, Credit Card, etc.
• TotalCollected: The sum of all payments for that specific type on that specific day.

How to Customize

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

• Change the Date Range: Modify the dates inside the single quotes in the first two lines. For example, change '2026-04-01' to '2026-01-01' to see data for the entire year.
• Filter by Clinic: If you have multiple locations and want to see data for only one, add this line right before the GROUP BY clause:
  AND payment.ClinicNum = 1
  (Replace "1" with your specific Clinic Number found in Setup > Clinics).
• Filter by Provider: If you need to see collections attributed to a specific provider, add this line before the GROUP BY clause:
  AND payment.PayNum IN (SELECT PayNum FROM paysplit WHERE ProvNum = 12)
  (Replace "12" with the Provider Number found in Setup > Providers).

Variations

If you need to see which individual patients made up these totals, you can use this variation to list the patient names alongside their payments:

SET @FromDate = '2026-04-01';
SET @ToDate = '2026-04-02';

SELECT 
    payment.PayDate,
    patient.LName,
    patient.FName,
    payment.PayType,
    payment.PayAmt
FROM payment
INNER JOIN patient ON payment.PatNum = patient.PatNum
WHERE payment.PayDate BETWEEN @FromDate AND @ToDate
ORDER BY payment.PayDate, patient.LName;

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.