November 14, 2017 at 2:13 am
I have a Transaction table having the structure as follows:
DECLARE @Transtable TABLE( ID INT IDENTITY(1,1) NOT NULL, DATE DATETIME, DEBIT INT, CREDIT INT)
INSERT INTO @Transtable VALUES(1, '2017-11-01', NULL, 100)
INSERT INTO @Transtable VALUES(2, '2017-11-02', 50, 200)
INSERT INTO @Transtable VALUES(3, '2017-11-03', 100, NULL)
INSERT INTO @Transtable VALUES(3, '2017-11-04', 500, 1000)
My requirement is to add a running balance column in the table
with the values to be shown as
100, 250,150,650
Kindly help on this.
November 14, 2017 at 2:31 am
I just typed "running balance query" into my favourite search engine - it's amazing what you can find with a bit of research.
Now, are you really on SQL Server 2008?
John
November 14, 2017 at 2:48 am
John Mitchell-245523 - Tuesday, November 14, 2017 2:31 AMI just typed "running balance query" into my favourite search engine - it's amazing what you can find with a bit of research.Now, are you really on SQL Server 2008?
John
The OP has posted a fair amount of their topics in the 2008 forum, so I believe they likely are (unfortunately). This kind of work is easily achievable with SQL Server 2012 onwards with the OVER clause.
One option is to use a triangular join, but, those can perform awfully. The other option is using the "quirky update", however, this isn't something I've ever used: http://www.sqlservercentral.com/articles/T-SQL/68467/
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 2:52 am
GOT IT
SELECT t2.Date,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM TRANSTABLE t1
INNER JOIN TRANSTABLE t2
ON t1.Date <= t2.Date
GROUP BY t2.Date, t2.Credit, t2.Debit
November 14, 2017 at 2:55 am
Thom A - Tuesday, November 14, 2017 2:48 AMJohn Mitchell-245523 - Tuesday, November 14, 2017 2:31 AMI just typed "running balance query" into my favourite search engine - it's amazing what you can find with a bit of research.Now, are you really on SQL Server 2008?
John
The OP has posted a fair amount of their topics in the 2008 forum, so I believe they likely are (unfortunately). This kind of work is easily achievable with SQL Server 2012 onwards with the OVER clause.
One option is to use a triangular join, but, those can perform awfully. The other option is using the "quirky update", however, this isn't something I've ever used: http://www.sqlservercentral.com/articles/T-SQL/68467/
If you can kindly post the solution for 2012 version, it will be a learning for me.
My work environment is still in 2008 version
November 14, 2017 at 2:59 am
VSSGeorge - Tuesday, November 14, 2017 2:52 AMGOT IT
SELECT t2.Date,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM TRANSTABLE t1
INNER JOIN TRANSTABLE t2
ON t1.Date <= t2.Date
GROUP BY t2.Date, t2.Credit, t2.Debit
There's no need for the COALESCE there, SQl Server automatically omits NULL in aggragates, so that's unneeded overhead.
The solution you have there is the Triangular Join route. If you're using small datasets, then it'll work "fine" (it'll be expensive, but still pretty quick), however, I'd suggest having a look at the link above.
VSSGeorge - Tuesday, November 14, 2017 2:55 AMIf you can kindly post the solution for 2012 version, it will be a learning for me.
My work environment is still in 2008 version
Have a Google yourself. "SQL Server Running Total". The articles are everywhere, like John said. You're looking for a solution using ROWS BETWEEN. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 3:00 am
VSSGeorge - Tuesday, November 14, 2017 2:55 AMIf you can kindly post the solution for 2012 version, it will be a learning for me.
Best way to learn is to try it yourself, then post here and we'll help you if there's anything in particular you don't understand. You need to use SUM with an OVER (ROWS BETWEEN...) clause.
John
November 14, 2017 at 3:13 am
Thom A - Tuesday, November 14, 2017 2:59 AMVSSGeorge - Tuesday, November 14, 2017 2:52 AMGOT IT
SELECT t2.Date,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM TRANSTABLE t1
INNER JOIN TRANSTABLE t2
ON t1.Date <= t2.Date
GROUP BY t2.Date, t2.Credit, t2.DebitThere's no need for the COALESCE there, SQl Server automatically omits NULL in aggragates, so that's unneeded overhead.
You're right, Thom - normally there'd be no need. But since he's doing a SUM of credit - debit, if either of those is NULL, the difference will also be NULL and so the results will be wrong. What I'd question is why these two columns allow NULLs at all - what does a NULL credit or debit mean?
November 14, 2017 at 3:19 am
John Mitchell-245523 - Tuesday, November 14, 2017 3:13 AMYou're right, Thom - normally there'd be no need. But since he's doing a SUM of credit - debit, if either of those is NULL, the difference will also be NULL and so the results will be wrong. What I'd question is why these two columns allow NULLs at all - what does a NULL credit or debit mean?
Ahh, good point. Perhaps:SUM(t1.credit) - SUM(t1.debit)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 3:27 am
Thom A - Tuesday, November 14, 2017 3:19 AMAhh, good point. Perhaps:
SUM(t1.credit) - SUM(t1.debit)
Yes, that should work.
John
November 14, 2017 at 6:55 am
VSSGeorge - Tuesday, November 14, 2017 2:52 AMGOT IT
SELECT t2.Date,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM TRANSTABLE t1
INNER JOIN TRANSTABLE t2
ON t1.Date <= t2.Date
GROUP BY t2.Date, t2.Credit, t2.Debit
Since you're in the learning mode, learn about the "Triangular Join" that you've just created and how it can bring a server to it's knees in an exponential (x2+x)/ 2 fashion.
Hidden RBAR: Triangular Joins
If you post the CREATE TABLE statement along with ALL the indexes the table has and identify the columns to be used for the running total, I can show you the fastest way to create such a running total in pre-2012 systems.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply