SQL Puzzle: SQL Advance Query - To generate the account balance column for Bank Accounts

SQL Puzzle: SQL Advance Query – To generate the account balance column for Bank Accounts

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to prepare the report on bank account transaction like generate the data of AccountBalance column where for CR plus the transaction value and for DR minus the transaction value.

Input Data:

TransactionDate AccName Type Amount

--------------- ---------- ---- -------------

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

Expected Output:

TransactionDate AccName Type Amount AccountBalance

--------------- ---------- ---- --------------------- ---------------

2017-01-01 Anvesh CR 60000.00 60000.00

2017-02-01 Anvesh DB 8000.00 52000.00

2017-03-01 Anvesh CR 8000.00 60000.00

2017-04-01 Anvesh DB 5000.00 55000.00

2017-01-01 Nupur CR 10000.00 10000.00

2017-02-02 Nupur CR 8000.00 18000.00

2017-03-03 Nupur DB 8000.00 10000.00

Create a table with sample data:

CREATE TABLE AccountBalance

(

TransactionDate DATE

,AccName VARCHAR(10)

,Type VARCHAR(2)

,Amount MONEY

)

INSERT INTO AccountBalance

VALUES

('2017-01-01','Anvesh','CR','60000')

,('2017-02-01','Anvesh','DB','8000')

,('2017-03-01','Anvesh','CR','8000')

,('2017-04-01','Anvesh','DB','5000')

,('2017-01-01','Nupur','CR','10000')

,('2017-02-02','Nupur','CR','8000')

,('2017-03-03','Nupur','DB','8000')

Solution:

;WITH CTE AS

(

SELECT TransactionDate , AccName , Type

, CASE WHEN Type = 'DB' THEN AMOUNT * -1 ELSE AMOUNT END Amount, Amount Amt

,ROW_NUMBER() OVER (PARTITION BY AccName ORDER BY (SELECT NULL)) rnk

FROM AccountBalance

)

SELECT TransactionDate , AccName , Type , Amt Amount

, (SELECT SUM(Amount) FROM CTE c2 WHERE c2.AccName = c1.AccName AND c2.rnk <= c1.rnk ) AccountBalance

FROM CTE c1

Please try the different solution for this puzzle and share it via comment...

Jun 17, 2019Anvesh Patel

Comments: 5

Ankesh Patel

July 15, 2019 at 9:01 am

Update AccountBalance
set amount=amount*-1
where type=’DB’

–then give rank based on the account in the given table itself

select
TransactionDate,
Accname,
Type,
Amount,
IDs,
(select sum(amount) from AccountBalance a where a.accname=b.accname and a.rank<=b.rank) real_time_balance
from AccountBalance b

Reply Cancel

Deepak Tomar

July 18, 2019 at 10:06 am

Hi Avnesh ,

There is a window function that can do the same task as well.

;WITH cte AS (
SELECT
[TransactionDate]
,[AccName]
,[Type]
,[Amount]
,CASE WHEN [Type] = ‘DB’ THEN -1 * [Amount]
ELSE [Amount] END AS [NewAmount]
FROM AccountBalance

)

SELECT
[TransactionDate]
,[AccName]
,[Type]
,[Amount]
, SUM(cte.NewAmount) OVER (PARTITION BY [AccName] ORDER BY [TransactionDate] ROWS UNBOUNDED PRECEDING) AS [AccountBalance]
FROM cte

Reply Cancel

Sai Nath

August 11, 2019 at 7:18 am

; WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY ACCNAME ORDER BY (SELECT 1)) AS RW
FROM
AccountBalance
)

SELECT
*,
SUM(CASE WHEN TYPE = ‘DB’ THEN -AMOUNT ELSE AMOUNT END) OVER(PARTITION BY ACCNAME ORDER BY RW)
FROM
CTE

Reply Cancel

Samrat

October 24, 2019 at 7:45 am

SELECT * ,
SUM(CASE WHEN TYPE = ‘DB’ THEN Amount*-1 ELSE Amount END) OVER (PARTITION BY AccName ORDER BY AccName,TransactionDate ) AS BALANCE
FROM dbo.ACCOUNTBALANCE

Reply Cancel

Anand Kumar

May 25, 2020 at 3:25 am

Is it possible to get a blank line after change in accname ?

Reply Cancel

SQL Puzzle: SQL Advance Query – To generate the account balance column for Bank Accounts

Leave a Reply Cancel reply

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !