ROUND or CONVERT the output of the POWER function

Question

ROUND or CONVERT the output of the POWER function

Viewing 3 posts - 16 through 17 (of 17 total)

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

Chrissy321 SSCoach Points: 15698 More actions · Answer 1

I went forward with the "Convert to float method with rounding to 6 (significant figures)" method above. It matches the Excel calc, avoids arithmetic overflow errors, produces output with enough precision and I can control the precision of the data output to the downstream system. Appreciate everyone here who is willing to share their expertise.

Jonathan AC Roberts SSCoach Points: 17901 More actions · Answer 2

If you want to avoid an error if zero is passed in you can use this with an IIF:

ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C)))))

Chrissy321 SSCoach Points: 15698 More actions · Answer 3

Jonathan AC Roberts wrote:

If you want to avoid an error if zero is passed in you can use this with an IIF:
ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C)))))

Yes I have incorporated IIF into my current code but neglected to include that in my most recent posted example.

Here is how I am documenting this according my somewhat limited understanding of the method.

Thanks Again.

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

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