Oracle - Calculate Previous balance using Current Balance

I have 2 tables.

1. Table TRAN has Account_no, Transaction_date and Transaction_Amount
2. Table BAL has Account_no and Current_balance

I want to populate results from table TRAN for an account number along with a running balance using the current_balance from BAL table. (the last row should have the current_balance and the previous row should have the previous balance that is current_balance minus the transaction amount)

Here is how the data looks like in both the tables:

Table TRAN (1) and BAL (2)

Here is how i want the output to look like:

The Output I Desire

Here is the code i tried using:

SELECT 
A.ACCOUNT_NO, 
A.TRANSACTION_DATE, 
A.TRANSACTION_AMOUNT, 
B.CURRENT_BALANCE - LAG(A.TRANSACTION_AMOUNT) OVER (ORDER BY A.TRANSACTION_DATE) RUNNING_BALANCE 
FROM TRAN A 
INNER JOIN BAL B ON A.ACCOUNT_NO=B.ACCOUNT_NO
WHERE A.ACCOUNT_NO='11111111';

I get the below output:

Query Result

What changes do I make to the query to obtain "the output I desire"?

After many attempts I finally figured out how to populate the previous balance by subtracting the transaction_amount from the current_balance.

SELECT 
A.ACCOUNT_NO, 
A.TRANSACTION_DATE, 
A.TRANSACTION_AMOUNT, 
B.CURRENT_BALANCE - (SUM(A.TRANSACTION_AMOUNT) OVER (PARTITION BY A.ACCOUNT_NO ORDER BY A.TRANSACTION_DATE DESC)) +  A.TRANSACTION_AMOUNT RUNNING_BALANCE
FROM TRAN A 
INNER JOIN BAL B ON A.ACCOUNT_NO=B.ACCOUNT_NO
WHERE A.ACCOUNT_NO='11111111'
ORDER BY A.ACCOUNT_NO, A.TRANSACTION_DATE;

Please upvote this answer if you find it helpful.

The query displays the history from Table A, joins Table B and calculates the running_balance using the current_balance.

The SUM function is used to calculate the previous balance by subtracting the transaction_amount from the current_balance.

The OVER clause is used to specify the partitioning and ordering of the rows for the calculation of the running_balance.

The ORDER BY clause is used to order the rows in descending order by transaction_date so that the latest transaction has the current_balance.

The + A.TRANSACTION_AMOUNT part of the query adds back in the transaction_amount to get the previous balance.

Why are you keeping two tables when you can keep a running balance from one table?

I did something similar. You may want to look at my solution.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS 
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL  UNION ALL 
SELECT 'Z098555', 'Peter', 'Clemenza','Y' FROM DUAL;
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS 
SELECT 'THVQD6M9LR7AVK', 'E379466', 1, 'Y' FROM DUAL  UNION ALL 
SELECT '0Z76WT5NTLRZPTW',
'Z098555', 1, 'Y' FROM DUAL;
create table transactions (
transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
account_number VARCHAR2(15),
transaction_type varchar2(1) DEFAULT 'C',
transaction_amount NUMBER(10,2),
transaction_date DATE DEFAULT SYSDATE 
);
insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT '0Z76WT5NTLRZPTW', 'D', (LEVEL * 100.57), date '2023-04-02' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT '0Z76WT5NTLRZPTW', 'C', (LEVEL * 25.26), date '2023-04-04' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 50.10), date '2023-05-10' + level * interval '1 7' day to hour from dual
connect by level <= 13
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 33.11), date '2023-05-10' + level * interval '1 7' day to hour from dual
connect by level <= 9;
CREATE OR REPLACE FUNCTION   get_customer_balance
(  i_customer_id   IN   customers.customer_id%TYPE
)
RETURN  transactions.transaction_amount%TYPE
IS
v_balance   transactions.transaction_amount%TYPE;
BEGIN
SELECT SUM (
CASE  t.transaction_type
WHEN  'C'
THEN  -t.transaction_amount
ELSE  t.transaction_amount
END 
)
INTO   v_balance
FROM	 customer_accounts  ca
JOIN	 transactions  	    t  ON  t.account_number  = ca.account_number
WHERE  ca.customer_id  = i_customer_id  -- one customer
OR     ca.customer_id  IS NULL;         -- all customers
RETURN v_balance;
END  get_customer_balance;
/
CREATE OR REPLACE FUNCTION get_account_balance(
i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
SELECT SUM(
CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
* transaction_amount
)
INTO   v_balance
FROM   transactions
WHERE  account_number = i_account_number -- one account
OR     i_account_number IS NULL;         -- all accounts
RETURN v_balance;
END;
/
SELECT CA.ACCOUNT_NUMBER,
C.FIRST_NAME,
C.LAST_NAME, 
CA.IS_ACTIVE,
get_account_balance(ca.account_number) AS balance
FROM   CUSTOMER_ACCOUNTS CA
INNER JOIN customers c
ON ca.customer_id = c.customer_id;
/* transaction history for an account */
WITH  daily_summary  AS
(
SELECT	
account_number,
transaction_date
,	 SUM (DECODE (transaction_type, 'C', transaction_amount, 0))	AS credit_total
,	 SUM (DECODE (transaction_type, 'D', transaction_amount , 0))	AS debit_total
FROM	 transactions
GROUP BY  account_number, transaction_date 
)
SELECT   d.*
,	 SUM (debit_total - credit_total)
OVER (ORDER BY transaction_date) AS balance_to_date
FROM    daily_summary d
WHERE account_number = 
'0Z76WT5NTLRZPTW'
ORDER BY transaction_date;