chisholmd (12/3/2012)
Hi all, I have a formula that works fine in Excel but not in SQL. Hopefully someone knows of some gotcha that I am not in this situation.When I run this in SQL I get 0.00 as a result but when I run it in Excel I get the expected value of 4020.73~
select ((26.7*((504.682+14.566)/14.73)*(520/(460+64))*(1/POWER((1/(0.99877-(0.00000072531*504.682*64)+(0.00013027*504.682))),2)))+(19.5*((1751.84+14.566)/14.73)*(520/(460+67))*(1/POWER((1/(0.99877-(0.00000072531*1751.84*67)+(0.00013027*1751.84))),2))))
Is anyone aware of subtle differences in how SQL evaluates expressions compared to Excel?
Within that complex expression are multiplications by the result of the following two computations:
SELECT (520 / (460 + 64))
SELECT (520 / (460 + 67))
Where both arguments to the division operator are integers, SQL Server performs integer division, returning zero in both cases. See http://msdn.microsoft.com/en-us/library/ms175009.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi