Click here to Skip to main content
15,920,632 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
I want Balance Like this in SQL Query How to do this..??
+------+-------+--------+---------+
|   Id | debit | credit | balance |
+------+-------+--------+---------+
|    1 |    10 |      0 |      10 |
|    2 |     0 |     40 |     -30 |
|    3 |    50 |      0 |      20 |
|    4 |     0 |     10 |      10 |
|    5 |     0 |     10 |       0 |
+------+-------+--------+---------+ 


What I have tried:

SELECT x.Id
     , x.debit
     , x.credit
     , SUM(y.Balance) balance 
  FROM
     ( 
       SELECT *,debit-credit bal FROM Ledger
     ) x
  JOIN
     ( 
       SELECT *,debit-credit bal FROM Ledger
     ) y
    ON y.Id<= x.Id
 GROUP 
    BY x.Id,x.debit,x.credit;
Posted
Updated 27-Mar-22 19:56pm
Comments
PIEBALDconsult 19-Feb-18 9:04am    
Look into Recursive Common Table Expressions.

Agree with PIEBALDConsult, but I'll add this:

The balance for each successive row depends on the ending balance from the previous row. You can either write SQL to loop through each transactions one at a time or, as has been suggested, look into recursive expressions.

Try one or the other and then post what you've tried.
 
Share this answer
 
I'd strongly recommend to read this: Calculating simple running totals in SQL Server[^].
 
Share this answer
 
You can use the same concept from here: How do I account for null values in a running total?[^]

Only different is, first the query need to find the different between the debit and credit, then run the running total calculation. See below as an example.
SQL
DECLARE @RunTotalTestData TABLE  (
   Id    int not null identity(1,1) primary key,
   Debit int null,
   Credit int null
);
 
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (10, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 40);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (50, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (100, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 110);
;WITH tempDebitCredit AS (
	SELECT a.id, a.debit, a.credit, a.Debit - a.Credit 'diff'
	FROM @RunTotalTestData a
)
SELECT a.id, a.Debit, a.Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a,
       tempDebitCredit b
WHERE b.id <= a.id
GROUP BY a.id,a.Debit, a.Credit


Output:
id	Debit	Credit	Balance
1	10	     0	     10
2	0	     40	    -30
3	50	     0	     20
4	0	     10	     10
5	0	     10	      0
6	100	     10	     90
7	0	     10	     80
8	0	     110	-30
 
Share this answer
 
Comments
Akshada Sane 20-Feb-18 6:17am    
Thank you very Much
kirthiga S 20-Sep-18 8:18am    
Nice one
select
x.id,
(sum(y.bal)-x.vin)+x.vout as Opening_Balance,
x.vin,x.vout,sum(y.bal) Closing_Balance
from
(select *,vin-vout bal from tblbalancesheet) x
join
(select *,vin-vout bal from tblbalancesheet) y
ON y.id<=x.id
group by x.id,x.vin,x.vout,x.vdate
 
Share this answer
 
I have added the ability to get the Brought Forward balance to Brian Solution above

SQL
WITH tempDebitCredit AS (
Select 0 As Details_ID, null As Creation_Date, null As Reference_ID, 'Brought Forward' As Transaction_Kind, null As Amount_Debit, null As Amount_Credit, isNull(Sum(Amount_Debit - Amount_Credit), 0) 'diff'
From _YourTable_Name
where Account_ID = @Account_ID
And Creation_Date < @Query_Start_Date
Union All
SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, a.Amount_Debit, a.Amount_Credit, a.Amount_Debit - a.Amount_Credit 'diff'
FROM _YourTable_Name a
where Account_ID = @Account_ID
And Creation_Date >= @Query_Start_Date And Creation_Date <= @Query_End_Date
)

SELECT a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, 
a.Amount_Debit, a.Amount_Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a, tempDebitCredit b
WHERE b.Details_ID <= a.Details_ID
GROUP BY a.Details_ID, a.Creation_Date, a.Reference_ID, a.Transaction_Kind, 
a.Amount_Debit, a.Amount_Credit
Order By a.Details_ID Desc

Tested on Microsoft SQL Server
 
Share this answer
 
I think the simple way is follow, this script run on SQL Server.

Test data:
DECLARE @TestData TABLE  (
   Id    int not null identity(1,1) primary key,
   Debit int null,
   Credit int null
);
 
INSERT INTO @TestData (debit, Credit) VALUES (10, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 40);
INSERT INTO @TestData (debit, Credit) VALUES (50, 0);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (100, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 10);
INSERT INTO @TestData (debit, Credit) VALUES (0, 110);


The main query:

select * from @TestData
DECLARE @i integer=1;
DECLARE @b integer=0;

declare @temp Table (
Id    integer not null,
Debit integer not null,
Credit integer not null,
Balance integer
)
while @i <= (select count(*) from @TestData)
begin 
 insert into @temp select * , (@b + debit) - credit as b from @TestData a where id = @i
 set @b = (select balance from @temp where Id = @i);
 set @i = @i+1
end
select * from @temp


And you can see the result as follow !
Input data:
Id	Debit	Credit
1	10	0
2	0	40
3	50	0
4	0	10
5	0	10
6	100	10
7	0	10
8	0	110


Output:
Id	Debit	Credit	Balance
1	10	0	10
2	0	40	-30
3	50	0	20
4	0	10	10
5	0	10	0
6	100	10	90
7	0	10	80
8	0	110	-30
 
Share this answer
 
v2
Comments
CHill60 28-Mar-22 3:52am    
Reason for my downvote: SQL Server is a set based language, there is very rarely, if ever, any need to use a loop

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900