Show Me Some of the Money

Finding Two-Digit Values in Four-Digit Columns

Jul 06, 2007
0 comments

Submitted By: Justin Stanley
Filed Under:

• MS SQL

Sometimes, you just expect things to be easy.

Like the first time I found myself actually writing code to solve a real life problem. I had an array of strings I'd populated by parsing a line of text, but the elements were in the wrong order. Couldn't figure out how to swap 'em around so, after a few hours of beating my head with and against various blunt objects, I gave up and asked one of our developers.

"Dude," I said. Developers must be addressed as "dude." And you must bring them an offering when you ask them for help. Cookies are usually sufficient, but be prepared to give your first-born.

"Got this array and I need some sort of function or method to reverse the order of the elements..."

"Uh, did you try 'array.Reverse'?"

Lesson learned: don't try to over-complicate things. If it feels like there should be an easy solution, there probably is. Just gotta know where to look.

So today, I ran into one of those situations. We have a SQL 2000 database with a table that contains product information (names, descriptions, prices, etc). One of the fields holds the current price of the specific item using the SQL money datatype. Order totals are calculated by multiplying the value in that price column by the order quantity for each item, then all line items are added together. Pretty straight forward most of the time... (cue the "bad stuff happened that someone should have foreseen" music.)

Turns out that, while the SQL money datatype uses four decimal places, other parts of the application only use two. Enter the rounding errors. For example, let's say a customer buys two items. One item has a four-digit price of $19.994 and the other is $7.993.

Now, when the ordering part of the system calculates the customer's cost, it rounds after it performs all it's calculations. In other words, it first adds the prices of the two items together to get a total of $27.987, rounding it up to $27.99. Unfortunately, another part of the application used by customer service folks to view orders reverses that procedure, rounding the line item prices first and adding later. In that case, it's adding $19.99 plus $7.99, returning $27.98.

That one cent difference doesn't seem like much, but it can certainly add up. Plus, there are fail-safes in the system that automatically place orders on hold when the two "totals" don't jive, delaying the shipment of those customer orders. All around bad juju just because people didn't handle the rounding properly.

The Powers That Be wanted to know which items were using more than two decimal places in the prices, so they turned to Our Hero for help. And here's where that whole you-expect-things-to-be-easy thing comes into play again.

Couldn't think of any function off the top of my head that would do what I needed, so I asked around. Sent an IM to a developer friend of mine, who suggested converting them to a VARCHAR, stripping off everything in front of the decimal point, then check out the lengths. Asked a coworker, and he suggested using a LIKE operator to search for anything that doesn't have two trailing zeros.

Problem with both of those was in the conversion from MONEY to VARCHAR. You end up losing the trailing zeros in the conversion (at least by default), so you need to add some more logic to the process to get a string you can manipulate properly. What to do... What to do...

Then it hit me. I suddenly remembered that abstract little mathematical operator, The Modulo (%). Sounds like the villain from a Xena episode, but in reality it's just a math operation that gives you the remainder after division. For example, 10 modulo 3 would be 1 (that is, 10/3 has a remainder of one). And the modulo of a division operation without a remainder is simply zero.

Thing about modulo is that the numbers involved have to be integers, so I couldn't use it right away to solve my problem. Instead, I had to convert my MONEY data into an INT, but I couldn't just let SQL do that. When you use CAST or CONVERT on a MONEY field, SQL just lops off everything after the decimal point (10.9 becomes 10, for instance).

Soooo... First off, I had to multiply the price by 10,000 to eliminate everything after the decimal point, then convert the result to an INT. So 19.99 becomes 199900 and 25.994 becomes 259940, etc. Second, I used the SQL Modulo operator to find out what the remainder would be if you divided the result by 100.

I realized that any of the original prices that had two or fewer decimal places would have to have at least two zeros on the end after being multiplied by 10,000 and, therefore, would have a modulo of zero if that result was divided by 100. Prices using three or four decimal places, however, wouldn't be evenly divisible by 100, meaning they had a non-zero modulo. After figuring that part out, writing the SQL statement to get the results was easy:

SELECT *

FROM Products

WHERE CONVERT(int, (price*10000))%100 <> 0

Executed that and ended up with a nifty little list of 23 potentially naughty items to give to the bosses. Haven't gone through the string manipulatin' suggestions I was given, but I suspect that my version probably runs a bit faster and definitely took less code.

Of course, it would be better if the rounding errors in the software could be fixed instead, but this at least gives us a way to avoid those errors in the short term until the bigger problem is solved.

And if anybody out there knows about some obscure FindTwoDigitMoneyThingies function or method they'd like to share with the class, I'm all ears.