ROUND or CONVERT the output of the POWER function

  • Hello smart people,

    I am trying to use the POWER function to return data to pass downstream in an ETL process. It's sufficient to return 2 decimal places but I can't seem to ROUND or CONVERT to DECIMAL.

    Given my data I want to return 3.97

    This seems to hint at some type of definitive way of handling this but the conclusion is not clear to me.

    https://dba.stackexchange.com/questions/6900/why-does-select-power10-0-38-0-throw-an-arithmetic-overflow-error

    Thanks if you can help.

    --Causes eror Arithmetic overflow error converting float to data type numeric.
    SELECT
    POWER
    (
    173.00/73.93,
    365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365)
    ) - 1

    --Returns 3.97657732995002E+33
    SELECT
    POWER
    (
    CONVERT(FLOAT,173.00/73.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365))
    ) - 1


    --Returns 3976577329950015839053078075539456.00!!
    SELECT
    CONVERT(DECIMAL(38,2),
    POWER
    (
    CONVERT(FLOAT,173.00/73.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365))
    ) - 1
    )

    --Still returns Returns 3.97657732995002E+33 doesn't round
    SELECT
    ROUND(
    POWER
    (
    CONVERT(FLOAT,173.00/73.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365))
    ) - 1
    ,2)

  • SELECT ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    FROM (SELECT POWER(
    CONVERT(FLOAT,173.00/73.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365))
    ) - 1 C) T;
  • NM... Jonathan pulled it off mathematically. (Nicely done, Jonathan!!!)

    I'd still like to know what question the math resolves.  Thanks.

    p.s. I know all the gazintas that Jonathan used to solve this but didn't know they'd maintain the Engineering Notation.  I learned something new today.  Thanks, Jonathan!

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

  • what exactly are  you trying to calculate? would be good if you could supply the original required formula as well as the english instructions of what it is its intention.

    the values and formula  you have will never give you 3.97

    breaking down your formula into its individual values you see that the calculation you are doing will only ever get near your desired value of 3.97 (and never to the exact value) when you do this calculation on the 194th day of the year (e.g. when 365/194.0 = 1.881443)

    SELECT
    POWER
    (
    CONVERT(FLOAT,173.00/73.93) -- step 1
    , CONVERT(FLOAT,
    (365/
    COALESCE(NULLIF
    (DATEDIFF(d,'2023-01-27'/*replacing GETDATE() with the date at time of posting */,'2023-01-31') -- step 2
    ,0)
    , 365
    )
    ) -- step 3 - 365 divided by step 2
    )
    ) -- step 4
    - 1
    , power(convert(float, 2.3400513) -- step 1 result
    , convert(float, 365)
    / convert(float, 4) -- step 2 result
    ) -- step 4 result
    --, power(2.3400513, 91) this is what the above formula is calculating as you can see by the results below
    -- breakdown of formulas
    , CONVERT(FLOAT,173.00/73.93) as step1_value
    , DATEDIFF(d,'2023-01-27'/*replacing GETDATE() with the date at time of posting */,'2023-01-31') as step2_value
    , 365/DATEDIFF(d,'2023-01-27'/*replacing GETDATE() with the date at time of posting */,'2023-01-31') as step3_value

    -- in order to get the desired value of 3.97 (4.97 -1) the following is the max value you can use for "power" on the expression
    , round(power(2.3400,1.885), 2) - 1 as desired_result
  • Thanks all. I am replicating an Excel formula translated into SQL by a business analyst. They refer to it as rate of return. Seems to provide the 'correct' values but won't run without error against my full data set which is several million rows.

    Now trying to handle 0 or negative values. Please feel free to speculate since I haven't provided a example. I'll try ti dig one up.

    --An invalid floating point operation occurred.
    --Flipped the numbers
    SELECT ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    FROM (SELECT POWER(
    CONVERT(FLOAT,73.93/173.00),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-01-31'),0)),365))
    ) - 1 C) T;

    --An invalid floating point operation occurred.
    SELECT ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    FROM
    (
    SELECT 0 AS C
    ) T

    --Handles 0
    SELECT
    CASE
    WHEN T.C = 0 THEN 0
    ELSE ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    END
    FROM
    (
    SELECT 0 AS C
    ) T

    --An invalid floating point operation occurred.
    --Not sure what to do about negatives.
    SELECT ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    FROM
    (
    SELECT -1 AS C
    ) T
  • 'Correct' answer for a negative.

    --Excel thinks the correct answer is -20
    SELECT
    POWER(
    CONVERT(FLOAT,80.00/102.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-12-31'),0)),365))
    ) - 1

    --An invalid floating point operation occurred.
    SELECT ROUND(T.C, 2 - FLOOR(LOG10(T.C)))
    FROM
    (SELECT
    POWER
    (
    CONVERT(FLOAT,80.00/102.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-12-31'),0)),365))
    ) - 1 C) T;
  • You can use the ABS function as you can't take the logarithm of a negative number for zero use an IIF:

    SELECT ROUND(T.C, 2 - FLOOR(IIF(T.C = 0, 0, LOG10(ABS(T.C)))))
    FROM
    (SELECT
    POWER
    (
    CONVERT(FLOAT,80.00/102.93),
    CONVERT(FLOAT,365/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-12-31'),0)),365))
    ) - 1 C) T;

     

  • Not being a math genius by any means, I'm still deeply interested in what the formula solves for?

     

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

  • can you give us the original unchanged Excel formula - if it is made up of multiple steps ask your analyst to aggregate it into one, and then give the explanation of what each of the parts is made of.

    it may be that there is a tiny thing missing in translation as even the lack of brackets can lead to these errors.

  • Chrissy321 wrote:

    It's sufficient to return 2 decimal places but I can't seem to ROUND or CONVERT to DECIMAL.

    Just a note: You are not rounding to 2 decimal places but 3 significant figures. For example, 3786567.981456 to 2 decimal places is 3786567.98 to 3 significant figures it is 379000 which appears to be what you want.

  • And do remember that the resulting number is actually huge coming in at E33 and isn't just 3.97.  Because of that, I'm seriously doubting this whole formula... especially since it appears to be tied to a temporal formula based on a non-leap year.

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

  • Jeff Moden wrote:

    And do remember that the resulting number is actually huge coming in at E33 and isn't just 3.97.  Because of that, I'm seriously doubting this whole formula... especially since it appears to be tied to a temporal formula based on a non-leap year.

    Yes, I can't imagine what it is for!

  • Yes it's a simplified rate of return function but not the more formalized IRR function which Excel has and others have done in tsql. Appreciate the help and will follow up once I do so more testing and validation.

  • I am evaluating the below methods with my full data set. Now 'rounding' to 6. The excel formula uses the excel power function with the caret symbol and matches the tsql formulas being presented here.

    DROP TABLE IF EXISTS #Test
    CREATE TABLE #Test (StartValue DECIMAL (30,6),ProjectedValue DECIMAL (30,6),StartDate DATE,ProjectedValueDate DATE)
    INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (102,150,'2022-12-05','2023-12-31')
    INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (73.93,173,'2022-12-05','2023-12-31')

    --Convert to float method
    SELECT
    POWER
    (
    CONVERT(FLOAT,ProjectedValue/StartValue),
    CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)
    ) - 1,
    StartValue,ProjectedValue,StartDate,ProjectedValueDate
    FROM #Test

    --Add decimals to denominator
    SELECT
    POWER
    (
    ProjectedValue/StartValue,
    365.000000/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)
    ) - 1,
    StartValue,ProjectedValue,StartDate,ProjectedValueDate
    FROM #Test

    --Convert to float method with rounding to 6 (significant figures)
    SELECT ROUND(T.C, 6 - FLOOR(LOG10(ABS(T.C)))),
    StartValue,ProjectedValue,StartDate,ProjectedValueDate
    FROM
    (
    SELECT
    POWER
    (
    CONVERT(FLOAT,ProjectedValue/StartValue),
    CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)
    ) - 1 C,
    StartValue,ProjectedValue,StartDate,ProjectedValueDate
    FROM #Test
    ) T;

Viewing 15 posts - 1 through 15 (of 17 total)

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