January 27, 2023 at 7:38 pm
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.
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)
January 27, 2023 at 10:56 pm
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;
January 27, 2023 at 11:02 pm
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
Change is inevitable... Change for the better is not.
January 27, 2023 at 11:04 pm
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
January 28, 2023 at 12:23 am
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
January 28, 2023 at 12:28 am
'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;
January 28, 2023 at 12:39 am
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;
January 28, 2023 at 12:39 am
Not being a math genius by any means, I'm still deeply interested in what the formula solves for?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2023 at 9:04 am
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.
January 29, 2023 at 3:35 pm
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.
January 29, 2023 at 5:40 pm
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
Change is inevitable... Change for the better is not.
January 29, 2023 at 5:57 pm
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!
January 29, 2023 at 6:23 pm
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
January 30, 2023 at 12:55 am
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.
January 30, 2023 at 3:25 pm
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