June 28, 2023 at 4:05 pm
Hello,
Any help appreciated. This code should perform a internal rate of return (IRR) calculation. It generates correct data based on what was provided to me using Excel's IRR function.
It looks like as GETDATE() gets close to my hard coded date the error is generated. Any suggestion to prevent the error would be appreciated.
SELECT
POWER
(
CONVERT(FLOAT,CONVERT(DECIMAL(30,6),322.000000)/NULLIF(4.079000,0)),
CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,GETDATE(),'2023-06-30'),0)),365)
)
-1
June 29, 2023 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 29, 2023 at 9:08 pm
You are attempting to compute the result of raising 78.94 to the power of 365, which evaluates to approximately 3.27E+692.
This value exceeds the maximum representable number in the float data type by approximately 1.83E+384 times. The maximum value of a float is 1.79E+308.
June 29, 2023 at 9:21 pm
is this still using the same formula you had on your original thread (https://www.sqlservercentral.com/forums/topic/round-or-convert-the-output-of-the-power-function) - or are you now trying to use the correct IRR
June 30, 2023 at 8:15 pm
is this still using the same formula you had on your original thread (https://www.sqlservercentral.com/forums/topic/round-or-convert-the-output-of-the-power-function) - or are you now trying to use the correct IRR
Yes, same formula you had on your original thread.Here is the code I have been using with the additional data that started causing the overflow.
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')
INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (29.210000,43.000000,'2023-01-31','2023-02-16')
--Causes Arithmetic overflow error converting expression to data type float.
--INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (4.079000,322.000000,'2023-06-28','2023-06-30')
--Use Round to reduce precision to approximately 6 decimal places
--The output of LOG10 which is the inverse of the POWER function is subtracted from the ROUND length to return significant digits
SELECT ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C))))),
T.C AS PowerOutput,
LOG10(T.C) AS Log10Output,
FLOOR(LOG10(T.C)) AS FloorLog10Output
,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;
June 30, 2023 at 8:53 pm
The overflow occurs because the exponent in the POWER function is extremely large due to the short time span and high growth rate.
The formula isn't designed to handle cases of extreme growth over a short period of time.
While it's not an ideal solution, one workaround is to check the values before the calculation and handle these cases separately:
SELECT ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C))))),
T.C AS PowerOutput,
LOG10(T.C) AS Log10Output,
FLOOR(LOG10(T.C)) AS FloorLog10Output,
StartValue,
ProjectedValue,
StartDate,
ProjectedValueDate
FROM (SELECT CASE WHEN ProjectedValue/StartValue > 30 THEN NULL -- Add your own condition based on your data.
ELSE POWER(CONVERT(FLOAT,ProjectedValue/StartValue),
CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)) - 1
END C,
StartValue,
ProjectedValue,
StartDate,
ProjectedValueDate
FROM #Test
) T
;
June 30, 2023 at 9:24 pm
Yes I went down that path. See below. The CASE is currently excluding my problematic data. The problematic data is actually caused by a data entry error. When I correct the data as I expect the analysts will (see my last insert moving the decimal point 2 places) I get a new error. So now I am in the unpleasant situation where correcting data will brake my production code.
So as you say the POWER function doesn't handle this well.
Suppressing errors doesn't seem to be an option as the code is in a view. The view is called by SSAS processing so I believe the only way to suppress the errors would be to do so on the call to the view and I don't believe there is an SSAS option to suppress.
SET ARITHABORT OFF;
SET ARITHIGNORE ON;
SET ANSI_WARNINGS OFF;
I guess I could encapsulate the code in a procedure that suppresses errors and stage the output in a table and have the view reference the staged data. Still valid data would be nulled out.
Appreciate your time and input as allows.
SET ARITHABORT OFF;
SET ARITHIGNORE ON;
SET ANSI_WARNINGS OFF;
SET ARITHABORT ON;
SET ARITHIGNORE OFF;
SET ANSI_WARNINGS ON;
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')
INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (29.210000,43.000000,'2023-01-31','2023-02-16')
--Causes Arithmetic overflow error converting expression to data type float.
INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (4.079000,322.000000,'2023-06-28','2023-06-30')
--The next record causes An invalid floating point operation occurred.
--INSERT INTO #Test (StartValue,ProjectedValue,StartDate,ProjectedValueDate) VALUES (4.079000,3.220000,'2023-06-28','2023-06-30')
--Use Round to reduce precision to approximately 6 decimal places
--The output of LOG10 which is the inverse of the POWER function is subtracted from the ROUND length to return significant digits
SELECT ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C))))),
T.C AS PowerOutput,
LOG10(T.C) AS Log10Output,
FLOOR(LOG10(T.C)) AS FloorLog10Output
,StartValue,ProjectedValue,StartDate,ProjectedValueDate
FROM
(
SELECT
--https://stackoverflow.com/questions/71012261/possible-to-detect-when-arithmetic-overflow-would-occur-before-powerx-y-is-exe
CASE WHEN FLOOR(1 + CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)* CAST(LOG(CONVERT(FLOAT,CONVERT(DECIMAL(30,6),ProjectedValue)/NULLIF(StartValue,0)),10) AS DECIMAL(38,10))) > 64 THEN NULL ELSE
POWER
(
CONVERT(FLOAT,ProjectedValue/StartValue),
CONVERT(FLOAT,365)/COALESCE((NULLIF(DATEDIFF(d,StartDate,ProjectedValueDate),0)),365)
) - 1
END AS C,
StartValue,ProjectedValue,StartDate,ProjectedValueDate
FROM #Test
) T;
June 30, 2023 at 9:53 pm
You are getting the new error because ProjectedValue / StartValue is less than 1 (3.22/4.079).
When you raise this to a number greater than 1 (182.5) you get a number very close to zero. You then subtract 1 from it and give it the alias "C". Then LOG10(-1) is attempted which is a complex number and causes the LOG10 function to error with:
Msg 3623, Level 16, State 1, Line 20
An invalid floating point operation occurred.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply