ROUND or CONVERT the output of the POWER function

Question

Post reply

ROUND or CONVERT the output of the POWER function

Chrissy321

SSCoach

Points: 15763

January 27, 2023 at 7:38 pm

#4142562

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)

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

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

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;

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

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

Chrissy321 SSCoach Points: 15763 More actions · Answer 4

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

Chrissy321 SSCoach Points: 15763 More actions · Answer 5

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

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;

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

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)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

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.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 11

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!

frederico_fonseca SSCoach Points: 16154 More actions · Answer 12

based on what the OP said above it is "rate of return" - can be done in SQL but not quite as presented above on the example.

https://towardsdatascience.com/calculating-internal-rate-of-return-irr-in-bigquery-75e6703e8ec3 or https://social.msdn.microsoft.com/Forums/sqlserver/en-US/868f5c56-bb32-4e8b-975b-ba72710c2215/using-sql-to-calculate-the-irr?forum=transactsql

Chrissy321 SSCoach Points: 15763 More actions · Answer 13

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.

Chrissy321 SSCoach Points: 15763 More actions · Answer 14

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;