Why is my SQL using Power not working?

Question

Why is my SQL using Power not working?

Adagio_b

Old Hand

Points: 330
More actions
October 3, 2019 at 6:30 am
Go to Answer
#3685556
I was given a task I thought would be easy. I was given an Excel file with a formula that I needed to replicate in MS SQL. Unfortunately I'm getting nowhere the same results. I did a Google search, but the solutions I found doesn't seem to work
The formula from Excel looks like the following: (Edit: Fixed the formula. Forgot the last 0)
```
=(0.889-7.49*(10^-3)*120+3.29*(10^-5)*120^2-4.77*(10^-8)*(120^3))/2*120
```
In SQL I wrote it like this:
```
SELECT
((0.889 - 7.49 * POWER(CAST(10 AS DECIMAL(18, 8)), -3) * 120 + 3.29 * POWER(CAST(10 AS DECIMAL(18, 8)), -5) * POWER(CAST(120 AS DECIMAL(18, 8)), 2) - 4.77 * POWER(CAST(10 AS DECIMAL(18, 8)), -8) * POWER(CAST(120 AS DECIMAL(18, 8)), 3)) / (2 * 120)) AS FullLineDecimal,
((0.889 - 7.49 * POWER(CONVERT(float, 10), -3) * 120 + 3.29 * POWER(CONVERT(float, 10), -5) * POWER(CONVERT(float, 120), 2) - 4.77 * POWER(CONVERT(float, 10), -8) * POWER(CONVERT(float, 120), 3)) / (2 * 120)) AS FullLineFloat
```
in the end some of the values should of course come from tables instead of being hardcoded
Based on what I found when searching for a solution was to convert the numbers to decimal or float as the result from POWER is the same type as you give it (e.g. giving it an int would give you an int in return)
The result I get in Excel is 22.9, but in SQL it's completely different:
FullLineDecimal = 0.001939
FullLineFloat = 0,00158972666666667
Any idea on what could be wrong and how to get the correct result?
- This topic was modified 5 years, 11 months ago by Adagio_b.
frederico_fonseca

SSCoach

Points: 16250
More actions
October 3, 2019 at 7:45 am
Answer
#3685578

you have 2 errors.
first the excel result is 2.2892064 and not 22.9 as you mention
second, and this is the important one, your formulas are not the same
last block on excel is 2 * 12 and on your sql you got (2 * 120)
floats give the same result - for decimal to give an approximate result you need to change decimal (18, 8) to be at least (20, 12) to get similar but not exactly equal results

Viewing 6 posts - 1 through 6 (of 6 total)

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

Adagio_b Old Hand Points: 330 More actions · Answer 1

Thanks, didn't see I copied the Excel wrong.

The correct Excel formula ends with 2 * 120, just like the SQL, which gives the result 22.892064. This is the result I'm trying to reach in SQL. It doesn't necessary have to have that many decimal places. A result rounded to one decimal is fine, but there's still a long way from 0.001939 to 22.9

I tried both float and decimal to see if there was any difference in the result, but both results are far away from what I'm searching for

Adagio_b Old Hand Points: 330 More actions · Answer 2

Wait... I believe I might have found the problem. The Excel formula divides by 2*120, while the sql divides by (2*120)

frederico_fonseca SSCoach Points: 16250 More actions · Answer 3

yes - that is what I said was wrong - both the 12/120 and the ()

Adagio_b Old Hand Points: 330 More actions · Answer 4

Doh, sorry. Missed that part of your message. I was too focused on the 12 vs 120 part. Marked yours as answer

Thanks for the help