Calculate the Running Total for the last five Transactions

  • Heh... Read it? I wrote it! 😉

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

  • Jeff Moden (11/28/2008)


    Heh... Read it? I wrote it! 😉

    Somebody deleted some posts because that message is not making any sense now!

  • That would be true. Some good fellow had the link to my Triangular join article with the comment that folks should take a gander. I didn't mean to scare him away with my comment. :blush:

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

  • I know what you mean, I used to take all those comments as they weredirected straight at me some time... forgetting the nature of the boeard :)... but that's makes you so god ;).

  • One line statement:-

    SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc

    The Network rtraffic would be lesser, as per below statistics:-

    Network Statistics

    Number of server roundtrips11.0000

    TDS packets sent from client11.0000

    TDS packets received from server11.0000

    Bytes sent from client378378.0000

    Bytes received from server771771.0000

  • Can you explain to me why the network traffic would change for the exact same result set... having the exact same amount of data?

    I know that query is about 30 bytes smaller... but I mean come on, that won'thurt any network any time soon!

  • arkhan (12/1/2008)


    One line statement:-

    SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc

    The Network rtraffic would be lesser, as per below statistics:-

    Network Statistics

    Number of server roundtrips11.0000

    TDS packets sent from client11.0000

    TDS packets received from server11.0000

    Bytes sent from client378378.0000

    Bytes received from server771771.0000

    Ummm.... lesser than what? I don't see any actual comparison here...

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

  • see Previous query's Network statistics (Byte sent and receide) hereunder:

    Network Statistics

    Number of server roundtrips11.0000

    TDS packets sent from client11.0000

    TDS packets received from server11.0000

    Bytes sent from client11841184.0000

    Bytes received from server816816.0000

  • I need some help here... how can the same amount of data sent on the wire require less resources, less work on the 2nd run... all that because of the query itself?

  • Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

  • stephenejones1960 (4/19/2014)


    Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

    Multiplying values is not a straight forward function in sql server, hence it has to be a different work around. One alternative can be using a pivot function. Please find the query for it:

    SELECT distinct p.id, max(p.[1]) * max(p.[2]) * max(p.[3]) * max(p.[4]) * max(p.[5])

    FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance ,ROW_NUMBER() OVER(Partition by a.id order by b.id) R

    FROM Accounts A

    cross JOIN Accounts B

    WHERE B.ID BETWEEN A.ID-4

    AND A.ID AND A.ID>4

    )T

    pivot (max(balance) for r in ([1],[2],[3],[4],[5]))P

    group by p.id

    Let me know if it helps.

    --Divya

  • stephenejones1960 (4/19/2014)


    Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

    You'll have to check for accuracy because of the nature of FLOAT calculations but if you sum the LOG of numbers, the ANTILOG (which is EXP in SQL Server) will be the PRODUCT of the numbers.

    For example,

    WITH

    cteSomeNumbers(N) AS

    (

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5

    )

    SELECT EXP(SUM(LOG(N)))

    FROM cteSomeNumbers

    ;

    The answer is 120.

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

  • That works for positive numbers > 0. If numbers can also be negative or 0, you can use this formula:

    CASE

    WHEN MIN(ABS(Value)) = 0 THEN 0

    ELSE EXP(SUM(LOG(ABS(NULLIF(Value,0))))) * ((SUM(CAST(SIGN(Value) AS int) - 1) % 4) + 1)

    END AS Product

    It looks daunting, but it's not that bad.

    The WHEN checks for at least one zero in the range of numbers to multiply and returns zero when that is the case.

    The ELSE has two parts. The first part is the formula Jeff gave, but on the absolute number of the value (ignore the NULLIF for now, I'll get to that later), so this produces the product of the absolute values. The second part counts the number of negative values in the range, and if it's odd it multiplies by -1 (otherwise by 1). The trick here is that SIGN returns -1 or 1, subtracting 1 makes that 0 or -2, and summing that results in (-2) times the number of negative values - the remainder after division by -4 is 0 when there's an even number of negatives, or -2 when it's odd. Add 1 to get a multiplier of either 1 or -1.

    The NULLIF is required to prevent errors. SQL Server can only evaluate the WHEN expression after grouping, but by then it has lost the individual values, so it will calculate parts of the expressions before grouping. One of those parts is LOG(ABS(Value)) - which would return an error for values of 0. The NULLIF replaces them with NULL (and because of the WHEN, the resulting intermediates will never be used).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 13 posts - 46 through 58 (of 58 total)

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