Post reply

summing up the previous row values up to date level

  • 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

  • July 16, 2010 at 10:20 pm

    #1194468

    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)
    Intro to Tally Tables and Functions

  • July 19, 2010 at 5:48 pm

    #1195235

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

    Rob Schripsema
    Propack, Inc.

  • July 20, 2010 at 4:57 am

    #1195402

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

  • July 22, 2010 at 1:53 pm

    #1197161

    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]

  • July 22, 2010 at 3:50 pm

    #1197231

    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)
    Intro to Tally Tables and Functions

  • July 24, 2010 at 10:21 am

    #1197874

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

  • July 26, 2010 at 6:37 am

    #1198162

    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)
    Intro to Tally Tables and Functions

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

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