Credit debit balance calculation on SQL

Question

1.44/5 (2 votes)

See more:

SQL-Server-2014

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 19-Feb-18 2:51am

Akshada Sane

Updated 27-Mar-22 19:56pm

Add a Solution

Comments

PIEBALDconsult 19-Feb-18 9:04am

Look into Recursive Common Table Expressions.

6 solutions

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.

Posted 19-Feb-18 3:16am

Tim Carmichael

Solution 2

I'd strongly recommend to read this: Calculating simple running totals in SQL Server[^].

Posted 19-Feb-18 3:44am

Maciej Los

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

Posted 19-Sep-18 19:28pm

Md.Tariqur Rahaman

Solution 5

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

Posted 12-Aug-19 1:46am

Member 10743924

Solution 6

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

Posted 27-Mar-22 19:56pm

Mohammad Jalal Fazli

Updated 27-Mar-22 20:20pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bryian Tan · Accepted Answer · 2018-02-19T04:58:00

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