Daily Deposit Report by Payment Type

If you need to reconcile your daily bank deposits against your practice management software, this open dental daily deposit query provides a clear breakdown of payments received by type. It is an essential tool for office managers who need to verify daily totals without manually digging through individual patient accounts.

The Query

Copy and paste the following code into your User Query window. You can adjust the SET variables at the top to define the specific date range you wish to review.

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

SELECT 
    p.PayDate,
    pt.ItemName AS PaymentType,
    SUM(p.PayAmt) AS TotalAmount
FROM payment p
INNER JOIN definition pt ON p.PayType = pt.DefNum
WHERE p.PayDate BETWEEN @FromDate AND @ToDate
GROUP BY p.PayDate, pt.ItemName
ORDER BY p.PayDate, pt.ItemName;

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

• PayDate: The date the payment was entered into the system.
• PaymentType: The category of payment (e.g., Cash, Check, Credit Card, EFT) as defined in your Definitions list.
• TotalAmount: The sum of all payments received for that specific payment type on that specific day.

How to Customize

• 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 want to see data for only one, add this line before the GROUP BY clause:
  AND p.ClinicNum = 1
  (Replace '1' with your specific ClinicNum. You can find this in Setup > Clinics).

Variations

If you need to see which specific patients contributed to these totals, you can use this variation to include patient names:

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

SELECT 
    p.PayDate,
    pat.LName,
    pat.FName,
    pt.ItemName AS PaymentType,
    p.PayAmt
FROM payment p
INNER JOIN patient pat ON p.PatNum = pat.PatNum
INNER JOIN definition pt ON p.PayType = pt.DefNum
WHERE p.PayDate BETWEEN @FromDate AND @ToDate
ORDER BY p.PayDate, pt.ItemName;

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.