Home Forums Article Discussions Article Discussions by Author Discuss content posted by Divya Agrawal Calculate the Running Total for the last five Transactions RE: Calculate the Running Total for the last five Transactions

  • April 20, 2014 at 12:49 pm

    #1707337

    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)