Math function question

Question

Math function question

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

True enough and good advice, Joe. The only time I see someone making the mistake of dividing MONEY by MONEY is when trying to determine quantity from a total cost and a unit cost. Still, it's (errors induced by datatype) something to be aware of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

CELKO (7/22/2010)
The underlying problem is that MONEY does rounding at every step (and only to 4 decimals and not the 5+ required for Euros). The rounding accumulates and screws you.
I thought this was common SQL Server knowledge and that is why nobody uses MONEY and SMALLMONEY any more. Hey, does everyone who beat me up about this know about BIT? That GROUP BY does not do a sort any more? Why UPDATE.. FROM.. is a disaster? etc?

Heh... yeah... I know about the problems with BIT and don't use it anymore (I did when I first started using SQL). GROUP BY does sort but only if you use WITH ROLLUP or WITH CUBE... but I don't rely on it for computations and I haven't tried the new ROLLUP() or CUBE(), yet. I disagree about UPDATE/FROM being a disaster unless someone screws up and allows "Halloweening" to come into play. Even with that, MERGE in both Oracle and SQL Server is a very effective replacement for joined UPDATEs so any arguments on my part in favor of UPDATE/FROM are pretty much moot now. 😉

I also agree with you on the MONEY datatype although I have to support the damned thing 😉 and other similar mistakes all the time. [font="Arial Black"]My apologies [/font]for making it sound like I agree with the use of the MONEY datatype for anything other than final storage (I even object to that but don't always have control over such things even after insisting). I just wanted you to post your the example since a lot of folks won't actually take the time to Google. Thanks for taking the time, Joe.

To be sure, since most folks use Excel or a decent calculator to do rate calculations with currency and other similar "accurate answer" calculations, I'll typically use at least 15 decimal places (even cheap calculators use 13) for all the calculations and interim answers. Only "final" answers will be rounded to the correct number of decimal places for display purposes. I try not to store such final answers in a display format... future tolerance buildup due to rounding and all that... just like you said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Oh... I almost forgot... special kudos to you on the "tone" of this one, Joe. Very well done and very much appreciated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)