How can I get a running total with opening balance ?

Question

0.00/5 (No votes)

See more:

i have this table tbl_CustJournals

J_Id Date Num Type custId Debit Credit Net
1 17/01/2018 5000 Invoice 2503 1472.78 0 1472.78
2 17/01/2018 1 Pay 2503 0 980 -980
3 07/02/2018 5 Invoice 2503 318.8 0 318.8
4 17/01/2018 2 Pay 2503 0 492 -492
5 09/02/2018 5 Invoice 270 4490 0 4490
6 07/02/2018 3 Pay 270 0 900 -900
7 10/02/2018 55 Invoice 65 14000 0 14000
8 17/01/2018 23 Invoice 65 520 0 520
9 17/01/2018 24 Invoice 65 525 0 525
10 17/01/2018 24 Invoice 65 1400 0 1400
11 17/01/2018 25 Invoice 65 1400 0 1400
12 17/01/2018 25 Invoice 65 975 0 975
13 17/01/2018 26 Invoice 65 3310 0 3310
14 17/01/2018 252 Invoice 65 3708.1 0 3708.1
15 04/02/2018 27 Invoice 2503 14000 0 14000
16 04/02/2018 28 Invoice 2503 14000 0 14000
17 17/02/2018 29 Invoice 65 640 0 640
18 07/02/2018 4 Pay 2503 0 999 -999
19 07/02/2018 4 Pay 2503 0 900 -900
20 07/02/2018 4 Pay 2503 0 101 -101
21 07/02/2018 4 Pay 2503 0 900 -900
22 17/01/2018 78 Invoice 65 180.99 0 180.99
23 17/01/2018 78 Invoice 65 231 0 231

i want to have A running total

What I have tried:

i tried this Procedure

ALTER procedure [dbo].[SP_Customer_Get_Balnance_By_Declare_Table]
@custcode int,
@datefrom  date,
@dateto date
as
DECLARE @st TABLE
(
	[Date] DATE ,
	J_Number int,
	J_Type varchar(25),
	[Description] varchar(max),
	Debit money,
	Credit money,
	Gl_Debits money,
	Net_Balance money
);
 
DECLARE @NetBalance money = 0;
 
INSERT @st([Date],J_Number,J_Type,[Description],Debit,Credit, Gl_Debits, Net_Balance)
	SELECT Journal_Date,Journal_Num,Journal_Type,[Description],Debit,Credit, GL_Debits, RunningTotal = 0
	FROM dbo.Tbl_Cust_Journals
	where Tbl_Cust_Journals.custId =@custcode and Journal_Date between @datefrom and @dateto
	ORDER BY Journal_Date;
 
UPDATE @st
	SET @NetBalance = Net_Balance = @NetBalance + Gl_Debits
	FROM @st;
 
SELECT [Date] ,J_Number ,J_Type ,[Description] ,Debit ,Credit , Gl_Debits , Net_Balance 
	FROM @st
	ORDER BY [Date];
	return

but the problem is when the custid is 2503 and the date is between "04/02/2018" and "07/02/2018" i want to have an oppining balance
can any one help me

Posted 23-Feb-18 5:53am

Abuamer

Updated 23-Feb-18 6:08am

Add a Solution

1 solution

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)

Richard Deeming · Accepted Answer · 2018-02-23T06:09:00

Solution 1

Assuming a recent version of SQL Server: (2012 or later)

SQL

SELECT 
    Journal_Date,
    Journal_Num,
    Journal_Type,
    [Description],
    Debit,
    Credit, 
    GL_Debits, 
    SUM(GL_Debits) OVER (ORDER BY Journal_Date ROWS BETWEEN Unbounded Preceding And Current Row) As Net_Balance
FROM 
    dbo.Tbl_Cust_Journals
WHERE
    custId = @custcode 
And 
    Journal_Date Between @datefrom And @dateto
ORDER BY 
    Journal_Date
;

OVER Clause (Transact-SQL) | Microsoft Docs[^]

Posted 23-Feb-18 6:09am

Richard Deeming

v2

Comments

Abuamer 23-Feb-18 13:59pm

thank you brother. there is another question . i want to get opening balance in the first row . which is the balance before the @datefrom. is that possible?

Richard Deeming 23-Feb-18 14:59pm

DECLARE @OB money;

SELECT @OB = SUM(GL_Debits)
FROM dbo.Tbl_Cust_Journals
WHERE custId = @custcode
And Journal_Date < @datefrom;

SELECT
    ...
    @OB + SUM(GL_Debits) OVER (ORDER BY Journal_Date ROWS BETWEEN Unbounded Preceding And Current Row) As Net_Balance
FROM
   ...