Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions
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
Improving my SQL BI Skills 
;WITH UPP_CTE AS(
SELECT Dt,CID,TransactionType,Amount,CASE WHEN TransactionType = ‘CR’ THEN Amount
WHEN TransactionType = ‘DB’ THEN -1* Amount END AS Computed_Bal FROM FindRunningBalance
)
SELECT SUM(Computed_Bal) OVER (PARTITION BY CID ORDER BY DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Running_Bal,DT,CID,TransactionType,Amount FROM UPP_CTE
LikeLiked by 1 person
;with cte
as
(
select dt,CID,TransactionType,case when TransactionType =’CR’ then Amount
when TransactionType=’DB’ then -1*Amount end as amount ,ROW_NUMBER() over (order by dt) as rwnum
from #FindRunningBalance
)
,cte2
as
(
select a.CID,b.rwnum,SUM(a.amount) as findRunningBalance from cte a
join cte b
on a.CID=b.CID
and a.rwnum0 then b.amount
else -1*b.amount end as Amount,
a.findRunningBalance
from cte2 a join cte b
on a.rwnum=b.rwnum
order by b.CID
LikeLiked by 1 person
;with cte
as
(
select dt,CID,TransactionType,case when TransactionType =’CR’ then Amount
when TransactionType=’DB’ then -1*Amount end as amount ,ROW_NUMBER() over (order by dt) as rwnum
from #FindRunningBalance
)
,cte2
as
(
select a.CID,b.rwnum,SUM(a.amount) as findRunningBalance from cte a
join cte b
on a.CID=b.CID
and a.rwnum0 then b.amount
else -1*b.amount end as Amount,
a.findRunningBalance
from cte2 a join cte b
on a.rwnum=b.rwnum
order by b.CID
LikeLiked by 1 person
;with cte
as
(
select dt,CID,TransactionType,case when TransactionType =’CR’ then Amount
when TransactionType=’DB’ then -1*Amount end as amount ,ROW_NUMBER() over (order by dt) as rwnum
from #FindRunningBalance
)
,cte2
as
(
select a.CID,b.rwnum,SUM(a.amount) as findRunningBalance from cte a
join cte b
on a.CID=b.CID
and a.rwnum0 then b.amount
else -1*b.amount end as Amount,
a.findRunningBalance
from cte2 a join cte b
on a.rwnum=b.rwnum
order by b.CID
LikeLiked by 1 person
HEY PAWAN HOW TO DELETE COMMENT
LikeLike
SELECT *,Amount,sum(amount)over(partition by cid order by dt rows between UNBOUNDED PRECEDING and current row)NewAmount FROM FindRunningBalance order by cid
LikeLike
SELECT *,
sum(CASE TransactionType when ‘CR’ then amount when ‘DB’ then 0 – Amount end) over(partition by cid order by dt rows between UNBOUNDED PRECEDING and current row) FindRunningBalance
FROM FindRunningBalance order by cid
LikeLiked by 1 person
with cte as
(
select *, row_number() over (partition by cid order by dt) as rn, case when transactiontype = ‘CR’ then amount when TransactionType =’DB’then -amount else 0 end as newamount from FindRunningBalance
)
select dt, cid, transactiontype, amount, sum(newamount) over (partition by cid order by rn) as findrunningbalance from cte
LikeLiked by 1 person
SELECT FORMAT(Dt, ‘dd-MM-yyyy’) Dt, CID, TransactionType, Amount,
SUM((case when TransactionType = ‘DB’ then -Amount else Amount end)) OVER (PARTITION BY CID ORDER BY Dt,TransactionType, Amount) RunningTotal
FROM dbo.FindRunningBalance
LikeLike