Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Running Balance Puzzle ] – In this puzzle we have to find the running balance like we have in our bank transaction statements. Please check out the sample input and expected output for details

Sample Input

Dt CID TransactionType Amount
01-01-2015 IshaMattoo CR 18000
02-01-2015 IshaMattoo DB 3000
03-01-2015 IshaMattoo CR 8000
04-01-2015 IshaMattoo DB 9000
04-01-2015 AvtaarKishan CR 1000
05-01-2015 AvtaarKishan CR 6000
05-01-2015 AvtaarKishan DB 5000

Expected Output

Dt CID TransactionType Amount FindRunningBalance
04-01-2015 AvtaarKishan CR 1000 1000
05-01-2015 AvtaarKishan CR 6000 7000
05-01-2015 AvtaarKishan DB 5000 2000
01-01-2015 IshaMattoo CR 18000 18000
02-01-2015 IshaMattoo DB 3000 15000
03-01-2015 IshaMattoo CR 8000 23000
04-01-2015 IshaMattoo DB 9000 14000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solutions in the comments section or send you solutions to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table
CREATE TABLE FindRunningBalance
(
Dt DATETIME,
CID VARCHAR(50),
TransactionType VARCHAR(2),
Amount MONEY
)

--Insert data
INSERT INTO FindRunningBalance(Dt,CID,TransactionType,Amount)
VALUES
( '2015-01-01','IshaMattoo','CR','18,000' ),
( '2015-01-02','IshaMattoo','DB','3,000' ),
( '2015-01-03','IshaMattoo','CR','8,000' ),
( '2015-01-04','IshaMattoo','DB','9,000' ),
( '2015-01-04','AvtaarKishan','CR','1,000' ),
( '2015-01-05','AvtaarKishan','CR','6,000'),
( '2015-01-05','AvtaarKishan','DB','5,000' )

--Verify Data
SELECT Dt,CID,TransactionType,Amount FROM FindRunningBalance

UPDATE – 24-Apr-2015 – Solution 1


--

;WITH CTE AS 
(
	SELECT Dt , CID , TransactionType 
			, CASE WHEN TransactionType = 'PAY' THEN AMOUNT * -1 ELSE AMOUNT END Amount, Amount Amt
			,ROW_NUMBER() OVER (PARTITION BY CID ORDER BY (SELECT NULL)) rnk
	FROM FindRunningBalance
)
SELECT  Dt , CID , TransactionType , Amt Amount
, (SELECT SUM(Amount) FROM CTE c2 WHERE c2.CID = c1.CID AND c2.rnk <= c1.rnk ) FindRunningBalance
FROM CTE c1

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements