Oracle SQL Patterns for Financial Data

Financial data in Oracle has a few patterns that show up so often that I’ve essentially memorised them. Date handling, end-of-day aggregations, running totals, finding transactions that don’t match. This is the collection.

Date arithmetic — don’t use SYSDATE directly in financial queries

SYSDATE returns the current date and time on the database server. Fine for logs, not fine for financial day-end processing where “today” might mean something specific (business day, settlement day, value date).

Store your processing date explicitly:

-- Find all transactions for a specific business date
SELECT *
FROM payment_transaction
WHERE TRUNC(transaction_date) = TO_DATE('2026-01-20', 'YYYY-MM-DD');

TRUNC strips the time component. Without it, transaction_date = TO_DATE('2026-01-20', 'YYYY-MM-DD') only matches midnight exactly.

Counting transactions by hour

Useful for seeing load distribution and for investigating incidents (“what was happening at 2pm?”):

SELECT
    TO_CHAR(transaction_date, 'YYYY-MM-DD HH24') AS hour_bucket,
    COUNT(*)                                       AS transaction_count,
    SUM(amount)                                    AS total_amount
FROM payment_transaction
WHERE TRUNC(transaction_date) = TO_DATE('2026-01-20', 'YYYY-MM-DD')
GROUP BY TO_CHAR(transaction_date, 'YYYY-MM-DD HH24')
ORDER BY 1;

Running total (cumulative sum)

For a transaction ledger showing balance after each entry:

SELECT
    transaction_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date, transaction_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
FROM account_transaction
WHERE account_id = :account_id
ORDER BY transaction_date, transaction_id;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means “sum everything from the first row up to and including the current row.” The ORDER BY inside the OVER clause determines the sequence — always include transaction_id as a tiebreaker when dates can repeat.

Reconciliation — finding what’s missing

You have an internal transaction table and a bank statement file. Find transactions in one that aren’t in the other:

-- Transactions in our system not in the bank statement
SELECT t.transaction_id, t.amount, t.transaction_date
FROM payment_transaction t
WHERE TRUNC(t.settlement_date) = TO_DATE('2026-01-19', 'YYYY-MM-DD')
  AND NOT EXISTS (
      SELECT 1
      FROM bank_statement b
      WHERE b.reference = t.transaction_id
        AND b.amount    = t.amount
  );

-- Bank statement entries not matched in our system
SELECT b.reference, b.amount, b.value_date
FROM bank_statement b
WHERE b.value_date = TO_DATE('2026-01-19', 'YYYY-MM-DD')
  AND NOT EXISTS (
      SELECT 1
      FROM payment_transaction t
      WHERE t.transaction_id = b.reference
        AND t.amount         = b.amount
  );

Run both. The first shows what we processed that the bank hasn’t seen. The second shows what the bank saw that we don’t have a record of. Both of these are problems, just different problems.

Duplicate detection

In payment systems, a duplicate is a very specific kind of problem. This finds potential duplicates by looking for the same account, amount, and currency within a time window:

SELECT
    account_id,
    amount,
    currency,
    COUNT(*) AS occurrences,
    MIN(transaction_date) AS first_seen,
    MAX(transaction_date) AS last_seen
FROM payment_transaction
WHERE transaction_date >= SYSDATE - INTERVAL '1' HOUR
GROUP BY account_id, amount, currency
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

Adjust the time window based on how your idempotency mechanism works and what your retry timeout is.

Update with a safety net

Before running any UPDATE on financial data in production, run the equivalent SELECT first to see exactly what you’re about to change:

-- See what you're going to update
SELECT transaction_id, status, amount
FROM payment_transaction
WHERE status = 'PENDING'
  AND transaction_date < SYSDATE - INTERVAL '24' HOUR;

-- Then update if the results look right
UPDATE payment_transaction
SET status = 'EXPIRED'
WHERE status = 'PENDING'
  AND transaction_date < SYSDATE - INTERVAL '24' HOUR;

COMMIT;

I have a colleague who built an actual tool that converts Oracle UPDATE statements to the equivalent SELECT before running them in production. Saved a few disasters. Might write that up separately.

Month-to-date and year-to-date aggregations

-- MTD totals per currency
SELECT
    currency,
    COUNT(*)   AS transaction_count,
    SUM(amount) AS total_amount
FROM payment_transaction
WHERE transaction_date >= TRUNC(SYSDATE, 'MM')  -- first day of current month
  AND transaction_date <  SYSDATE
  AND status = 'SETTLED'
GROUP BY currency
ORDER BY total_amount DESC;

TRUNC(SYSDATE, 'MM') returns midnight on the first day of the current month. TRUNC(SYSDATE, 'YYYY') does the same for the first day of the year.