Derived Column with value in previous row + current

Question

Post reply

Derived Column with value in previous row + current

SQLCurious

SSCrazy

Points: 2339
More actions
April 30, 2013 at 12:26 pm

#272011

I have a data set that has ID and Total fields. Now I need a result set that has a third column, "Adjusted Total". For the first row, "Adjusted Total" will have the same value as Total column. But from the next row onwards, it should have the total of previous "Adjusted Total" + current "Total".
Below are screenshots of a sample base data set as well as the desired result set.
Base Data
Desired Result set
Here is the SQL to setup base data into a table variable.
DECLARE @BaseData TABLE (ID INT, Total INT)
INSERT @BaseData (ID, Total)
VALUES
(1, 215),
(2, 86),
(3, 43),
(4, 101),
(5, 92),
(6, 64),
(7, 117),
(8, 59),
(9, 33),
(10, 72),
(11, 94),
(12, 108),
(13, 93),
(14, 88),
(15, 67)
SELECT * FROM @BaseData
Any help is appreciated.
Rex

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Something like this?

DECLARE @BaseData TABLE (ID INT, Total INT)

INSERT @BaseData (ID, Total)

VALUES

(1, 215),

(2, 86),

(3, 43),

(4, 101),

(5, 92),

(6, 64),

(7, 117),

(8, 59),

(9, 33),

(10, 72),

(11, 94),

(12, 108),

(13, 93),

(14, 88),

(15, 67)

select

bd1.ID,

bd1.Total,

bd1.Total + isnull(ds.Total,0) as AdjustedTotal

from

@BaseData bd1

outer apply (select bd2.Total from @BaseData bd2 where bd2.ID = bd1.ID - 1) ds(Total);

It could also be done using a self join.

SQLCurious SSCrazy Points: 2339 More actions · Answer 2

Lynn Pettis,

Thanks, but the "AdjustedTotal" should be a sum of previous "AdjustedTotal" and current total. Your solution is showing sum of Previous "Total" and current "Total".

I will try to play around with this and see if I can make changes to get what I want. Thanks, again.

Rex

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

RexHelios (4/30/2013)
Lynn Pettis,
Thanks, but the "AdjustedTotal" should be a sum of previous "AdjustedTotal" and current total. Your solution is showing sum of Previous "Total" and current "Total".
I will try to play around with this and see if I can make changes to get what I want. Thanks, again.
Rex

I did misread what you wanted. You want a running total. Start here and read the discussion that goes with the article: http://www.sqlservercentral.com/articles/T-SQL/68467/.

SQLCurious SSCrazy Points: 2339 More actions · Answer 4

Thanks, Lynn Pettis. I will refer to the article.

- Rex

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

RexHelios (4/30/2013)
Thanks, Lynn Pettis. I will refer to the article.
- Rex

And I can't stress enough, be sure to read the discussion as well. There is much to learned from it as well once you have worked your way through the article.