How to make balance of bank statement

coforge

Input:

Output:

Solution:

create table bank (TransactionDate varchar(20),AccName varchar(20),Type varchar(20),Amount integer);

insert into bank values ("2017-01-01","Anvesh","CR",60000.00),("2017-02-01","Anvesh","DB",8000.00),("2017-03-01","Anvesh","CR",8000.00),("2017-04-01","Anvesh","DB",5000.00),("2017-01-01","Nupur","CR",10000.00),("2017-02-02","Nupur","CR",8000.00),("2017-03-03","Nupur","DB",8000.00)

WITH balances AS (
  SELECT AccName, TransactionDate, Type, Amount,
         SUM(CASE WHEN Type = 'DB' THEN -Amount ELSE Amount END) 
         OVER (PARTITION BY AccName ORDER BY TransactionDate) AS AccBalance
  FROM bank
)
SELECT TransactionDate, AccName, Type, Amount, balance
FROM balances 
ORDER BY AccName, TransactionDate;

Noted:

The balances CTE uses the SUM function with a CASE statement to determine the cumulative AccBalance of each account by summing up the amounts of the transactions. The PARTITION BY clause in the OVER clause separates the calculation of the AccBalance for each account, so that the AccBalance for each account is calculated independently. The ORDER BY clause in the OVER clause ensures that the transactions are processed in the correct order, so that the AccBalance is calculated correctly.

The final SELECT statement queries the balances CTE to show the transaction date, account name, type of transaction, amount, and AccBalance for each transaction, ordered by account name and transaction date.