Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
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 12-Aug-19 1:46am
Comments
PIEBALDconsult 19-Feb-18 9:04am
   
Look into Recursive Common Table Expressions.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

I'd strongly recommend to read this: Calculating simple running totals in SQL Server[^].
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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.
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
   
Comments
Akshada Sane 20-Feb-18 6:17am
   
Thank you very Much
kirthiga S 20-Sep-18 8:18am
   
Nice one
Rate this:
Please Sign up or sign in to vote.

Solution 4

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

I have added the ability to get the Brought Forward balance to Brian Solution above

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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100