summing up the previous row values up to date level

Question

summing up the previous row values up to date level

jprabha.d

Ten Centuries

Points: 1086
More actions
July 16, 2010 at 9:52 pm

#97318

Hi All,
How Do we Sum up the Values As Per the Date in a Particular Month ,I am Designing the Report wher i have to Drill Down U pto the Date Level, I Need To Sum up the Percentage value From th Previous Dates and So on ...it has to continue.
If i take up the particular Date say 4/July/2010 -it should show me the value summing up all the Previous date that comes in the July Month Say as 48 %
when i drill down up to the level of the particular date ,it should show me the value upto date(i;e sum of all the values) instead of value on that date.
Please Let me know how can i write the sql query to get this result or is it possible to achieve in ssrs directly.
Thanks & regards
Jprabha

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 1

jprabha.d (7/16/2010)
Hi All,
How Do we Sum up the Values As Per the Date in a Particular Month ,I am Designing the Report wher i have to Drill Down U pto the Date Level, I Need To Sum up the Percentage value From th Previous Dates and So on ...it has to continue.
If i take up the particular Date say 4/July/2010 -it should show me the value summing up all the Previous date that comes in the July Month Say as 48 %
when i drill down up to the level of the particular date ,it should show me the value upto date(i;e sum of all the values) instead of value on that date.
Please Let me know how can i write the sql query to get this result or is it possible to achieve in ssrs directly.
Thanks & regards
Jprabha

I don't use SSRS but isn't there a RunningTotal function in SSRS?

--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)

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 2

Yes, there is a 'RunningValue' aggregate function that can be used for this.

Rob Schripsema
Propack, Inc.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

jprabha.d (7/16/2010)
Please Let me know how can i write the sql query to get this result

Post some sample data along with your query.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Joe Celko (7/21/2010)
For your own education,look up the ANSI/ISO Standard syntax for
SUM(<expression>)
OVER (PARTITION BY ..
ORDER BY ..
[ROW|RANGE] ..)
That last clause is how you do running computation in better SQLs.

Joe,

would you mind showing an SQL 2005 compliant example of your general code applied to the following table (expected result: running total for SomeValue order by SomeID)?

DECLARE @tbl TABLE

(

SomeID INT IDENTITY(1,1),

SomeValue INT

)

INSERT INTO @tbl(SomeValue)

SELECT 1 UNION ALL

SELECT 3 UNION ALL

SELECT 5 UNION ALL

SELECT 7

SELECT *

FROM @tbl

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 5

Joe Celko (7/21/2010)
For your own education,look up the ANSI/ISO Standard syntax for
SUM(<expression>)
OVER (PARTITION BY ..
ORDER BY ..
[ROW|RANGE] ..)
That last clause is how you do running computation in better SQLs.

Yep... and someday they'll make that work correctly in SQL Server. 😉

--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)

jprabha.d Ten Centuries Points: 1086 More actions · Answer 6

Thanks For the Solution i used Running value Function that is in SSRS,

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

jprabha.d (7/24/2010)
Thanks For the Solution i used Running value Function that is in SSRS,

A wise choice... how is it in the area of performance?

--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)