Need to add Balance of previous Row to Current Row

Question

Post reply

Need to add Balance of previous Row to Current Row

jameslauf

SSC Eights!

Points: 878
More actions
July 2, 2014 at 11:12 am

#292881

Hello I am novice to intermediate writer of T-SQL. I need help with a query.
Here is my current Query:
SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASC
and this is what is produces.
FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss
Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.
any and all help is much appreciated.
Thanks,
JL

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182882

July 2, 2014 at 11:15 am

#1726589

Quick question, what is your SQL Server version?

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Another quick question, are we talking a running total?

jameslauf SSC Eights! Points: 878 More actions · Answer 3

Version of SQL is SQL Server2008r2.

for Second Question I need both a running total and individual totals. 1st one is for posting type of Balance Sheet next one is for Profit & Loss the Balance sheet should be Running total for Ever and ever Profit & Loss will be individual Years. (does that answer you question?)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

I think you are looking for this:

-- (1) create sample data

DECLARE @values TABLE

(FISCALYEAR int not null,

ACCTPERIOD int not null,

TrialBalanceDebit decimal(16,5) not null,

POSTINGTYPE varchar(20) not null

);

INSERT @values VALUES

(2014, 201401, 282361372.13000, 'Profit & Loss'),

(2014, 201402, 227246272.86000, 'Profit & Loss'),

(2014, 201403, 315489534.33000, 'Profit & Loss'),

(2014, 201404, 287423793.76150, 'Profit & Loss'),

(2014, 201405, 256521290.76000, 'Profit & Loss'),

(2014, 201406, 65582951.30000, 'Profit & Loss');

-- (2) the solution

DECLARE @running_total decimal(16,5) = 0;

DECLARE @rt TABLE

(FISCALYEAR int not null,

ACCTPERIOD int not null,

TrialBalanceDebit decimal(16,5) not null,

POSTINGTYPE varchar(20) not null,

Running_total decimal(16,5) null

);

INSERT @rt (FISCALYEAR,ACCTPERIOD,TrialBalanceDebit,POSTINGTYPE)

SELECT *

FROM @values

ORDER BY ACCTPERIOD;

UPDATE @rt

SET @running_total = running_total = @running_total + TrialBalanceDebit

-- (3) The results

SELECT * FROM @rt;

Updated 14:49 CST:

-- (1) create sample data

USE tempdb

GO

IF OBJECT_ID('tempdb..#values') IS NOT NULL DROP TABLE #values;

CREATE TABLE #values

(FISCALYEAR int not null,

ACCTPERIOD int primary key,

TrialBalanceDebit decimal(16,5) not null,

POSTINGTYPE varchar(20) not null

);

INSERT #values VALUES

(2014, 201401, 282361372.13000, 'Profit & Loss'),

(2014, 201402, 227246272.86000, 'Profit & Loss'),

(2014, 201403, 315489534.33000, 'Profit & Loss'),

(2014, 201404, 287423793.76150, 'Profit & Loss'),

(2014, 201405, 256521290.76000, 'Profit & Loss'),

(2014, 201406, 65582951.30000, 'Profit & Loss');

-- (2) the solution

IF OBJECT_ID('tempdb..#rt') IS NOT NULL DROP TABLE #rt;

CREATE TABLE #rt

(FISCALYEAR int not null,

ACCTPERIOD int primary key,

TrialBalanceDebit decimal(16,5) not null,

POSTINGTYPE varchar(20) not null,

Running_total decimal(16,5) null

);

DECLARE @running_total decimal(16,5) = 0;

INSERT #rt (FISCALYEAR,ACCTPERIOD,TrialBalanceDebit,POSTINGTYPE)

SELECT *

FROM #values

ORDER BY ACCTPERIOD;

UPDATE #rt

SET @running_total = running_total = @running_total + TrialBalanceDebit

FROM #rt WITH (TABLOCKX)

OPTION (MAXDOP 1);

-- (3) The results

SELECT * FROM #rt;

Using the technique in this article[/url].

EDIT: Fixed my code (again) -- added PK to #rt.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

If you use the quirky update please make sure you read the article carefully. There are a number of extremely important requirements that must be met for this to work. The fine code by Alan omits several of those (1, 2, 4, 6).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

Sean Lange (7/2/2014)
If you use the quirky update please make sure you read the article carefully. There are a number of extremely important requirements that must be met for this to work. The fine code by Alan omits several of those (1, 2, 4, 6).

Please forgive my laziness; guilty as charged. :blush: I updated my code to include those important requirements.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

Alan.B (7/2/2014)
Sean Lange (7/2/2014)
If you use the quirky update please make sure you read the article carefully. There are a number of extremely important requirements that must be met for this to work. The fine code by Alan omits several of those (1, 2, 4, 6).
Please forgive my laziness; guilty as charged. :blush: I updated my code to include those important requirements.

By NO means was I trying to bust your chops. I was just trying to make 100% certain the OP didn't take your perfectly working code (works fine on super small datasets) and wonder why it didn't work on their much larger dataset. The topic is somewhat controversial (I side with Jeff on it too) and all the caveats are part of that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

Alan.B (7/2/2014)
Sean Lange (7/2/2014)
If you use the quirky update please make sure you read the article carefully. There are a number of extremely important requirements that must be met for this to work. The fine code by Alan omits several of those (1, 2, 4, 6).
Please forgive my laziness; guilty as charged. :blush: I updated my code to include those important requirements.

Still missing the CI on #RT, Alan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 9

Jeff Moden (7/2/2014)
Alan.B (7/2/2014)
Sean Lange (7/2/2014)
If you use the quirky update please make sure you read the article carefully. There are a number of extremely important requirements that must be met for this to work. The fine code by Alan omits several of those (1, 2, 4, 6).
Please forgive my laziness; guilty as charged. :blush: I updated my code to include those important requirements.
Still missing the CI on #RT, Alan.

Good grief - hopefully the third time is a charm ;-). Code fixed.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001