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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE AccountBalance ( TransactionDate DATE ,AccName VARCHAR(10) ,Type VARCHAR(2) ,Amount MONEY ) GO 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') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 |
;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...
