SQL calculations different than on calculator?

[Jennifer]

I'm running SQL query to caluclate projected food costs. The
calculation is this:

(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSales

Seems simple enough to me.

Using the following numbers, SQL comes up with a different answer than
what I do with a calulator. The data types are money. I'm sure there
is some reasonable explanation....right?

Reported Food Sales: 28096.4500
Plan Food Sales: 28608.4167
Full Plan Food Sales: 137702.0000

SQL Answer: 135237.1342
Calculator Answer: 135237.7308

Any ideas?

Thanks,
Jennifer

[Lyle Fairfield]

je**********@hotmail.com (Jennifer) wrote in
news:33**************************@posting.google.c om:

I'm running SQL query to caluclate projected food costs. The
calculation is this:

(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSales

Seems simple enough to me.

Using the following numbers, SQL comes up with a different answer than
what I do with a calulator. The data types are money. I'm sure there
is some reasonable explanation....right?

Reported Food Sales: 28096.4500
Plan Food Sales: 28608.4167
Full Plan Food Sales: 137702.0000

SQL Answer: 135237.1342
Calculator Answer: 135237.7308

Any ideas?

Thanks,
Jennifer

SQL does its calculations in a manner consistent with its currency
definition, that is it uses whole numbers mutiplied by 10000
It divides 280964500 by 286084167 and gets
0.98210433295317597915161799219738
It multiplies this by 10000 and use only the whole number part of that
answer: 9821.

It multiplies 9821 * 1377020000 and gets 13523713420000. It then divides
by (10000 * 10000) and gets 135237.1342. (note it would cut it to 4
decimals even if there were more in float).

Probably, you could emulate the calculator's results in SQL by casting
your currency values as floats before doing your calculating and then
casting the result back to currency before using or storing it. But I
find this to be a bit flaky and test various syntaxes until I get just
what I want. Sometimes I've had to use the currency as a string before I
convert it to a float, but this may be because I have missed something.
Try it on your calculator as I've described if you would like
verification.

[Hugo Kornelis]

On 8 Nov 2004 10:39:32 -0800, Jennifer wrote:

I'm running SQL query to caluclate projected food costs. The
calculation is this:

(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSales

Seems simple enough to me.

Using the following numbers, SQL comes up with a different answer than
what I do with a calulator. The data types are money. I'm sure there
is some reasonable explanation....right?

Reported Food Sales: 28096.4500
Plan Food Sales: 28608.4167
Full Plan Food Sales: 137702.0000

SQL Answer: 135237.1342
Calculator Answer: 135237.7308

Any ideas?

Thanks,
Jennifer

Hi Jennifer,

Lyle has already provided an explanation. A workaround is very simple to
find in this case. Since the division loses some precision, and this
precision loss is magnified by the multiplication that follows, the
workaround is to force SQL Server to do the multiplication first (you
won't lose much rpecision there), then finish with the division:

(FullPlanFoodSales * ReportedFoodSales) / PlanFoodSales

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Jennifer]

Lyle & Hugo :

Thank you both for your explanations! It's appreciated.

Thanks,
Jennifer
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<oq********************************@4ax.com>. ..

On 8 Nov 2004 10:39:32 -0800, Jennifer wrote:

I'm running SQL query to caluclate projected food costs. The
calculation is this:

(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSales

Seems simple enough to me.

Using the following numbers, SQL comes up with a different answer than
what I do with a calulator. The data types are money. I'm sure there
is some reasonable explanation....right?

Reported Food Sales: 28096.4500
Plan Food Sales: 28608.4167
Full Plan Food Sales: 137702.0000

SQL Answer: 135237.1342
Calculator Answer: 135237.7308

Any ideas?

Thanks,
Jennifer

Hi Jennifer,

Lyle has already provided an explanation. A workaround is very simple to
find in this case. Since the division loses some precision, and this
precision loss is magnified by the multiplication that follows, the
workaround is to force SQL Server to do the multiplication first (you
won't lose much rpecision there), then finish with the division:

(FullPlanFoodSales * ReportedFoodSales) / PlanFoodSales

Best, Hugo