SQL - how to shift decimal point

[RichardTisdall]

I am trying to compare product prices in two separate tables. The price in one table is data type real, 4 and in the other is smallmoney ,4. My prices are displayed like this.

(Real) (Smallmoney)
PriceSupp CPDPrice
930300.0 93.0300

Can anyone suggest how I can do some sort of conversion to be able to do the comparison.

I thought I could convert both to decimal or numeric bur this does not work. If I try to convert real to small money I get an overflow and if I convert small money to real the value changes to 93.0299. I suspect this causes further issues.

Any help greatly appreciated.

 

[JamesLean]

Converting to decimal should work. Just make sure you have large enough precision/scale:

DECLARE @real real,
	@smallmoney smallmoney

SELECT @real = 930300.0,
	@smallmoney = 93.0300

SELECT CAST(@real AS decimal(12, 4)),
	CAST(@smallmoney AS decimal(12, 4))

--James

 

[donutman]

Depending on what you want to do with the comparison, you might find what I do easier. Take the absolute value of the difference and check if it's less than .001, if so they are equal. No explicit conversion is necessary.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I'll point out that first he has to convert the real price to the correct one. 930300 <> 93.0300 Multiple the value in the real field by .0001 so that you are comparing apples and oranges.

And real is not an exact datatype, amounts you use for prices should never ever be stored in real or rounding errors will add up.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

With ABS() stuff there is still a chance for overflow in math sub-expression:

declare @PriceSupp real; set @PriceSupp = 930300.0
declare @CPDPrice smallmoney; set @CPDPrice = 93.03000

select case
when abs(@PriceSupp - @CPDPrice*10000) < .0001 then 'yep'
else 'nope' 
end

Of course, you can use 1e4 instead of 10000 - problem kinda solved.

Real causes data loss, smallmoney has too small precision. Decimal or preferably money (columns are price-related, right?) should do the trick.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[RichardTisdall]

Thanks for your help James and Karl

 

[RichardTisdall]

...and vongrunt and SQL sister I did forget about the Maths!!! Doh!

 

[donutman]

And real is not an exact datatype, amounts you use for prices should never ever be stored in real or rounding errors will add up.

That still bugs me to no end.

[Blue]DECLARE[/Blue] @Cnt [Blue]int[/Blue]
[Blue]DECLARE[/Blue] @RetailPrice [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @DiscountReal [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @DiscountMoney [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @DiscountPriceR [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @DiscountPriceM [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @TotalR [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @TotalM [Blue]money[/Blue]
[Blue]SET[/Blue] @TotalR[Gray]=[/Gray]0
[Blue]SET[/Blue] @TotalM[Gray]=[/Gray]0
[Blue]SET[/Blue] @RetailPrice[Gray]=[/Gray]1.00
[Blue]SET[/Blue] @DiscountReal[Gray]=[/Gray]1./3
[Blue]SET[/Blue] @DiscountMoney[Gray]=[/Gray]1./3
[Blue]SET[/Blue] @DiscountPriceR[Gray]=[/Gray][Gray]([/Gray]1[Gray]-[/Gray]@DiscountReal[Gray])[/Gray][Gray]*[/Gray]@RetailPrice
[Blue]SET[/Blue] @DiscountPriceM[Gray]=[/Gray][Gray]([/Gray]1[Gray]-[/Gray]@DiscountMoney[Gray])[/Gray][Gray]*[/Gray]@RetailPrice
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]0
[Blue]WHILE[/Blue] @Cnt[Gray]<[/Gray]100 [Blue]BEGIN[/Blue]
   [Blue]SET[/Blue] @Cnt[Gray]=[/Gray]@Cnt[Gray]+[/Gray]1
   [Blue]SET[/Blue] @TotalR[Gray]=[/Gray]@TotalR[Gray]+[/Gray]@DiscountPriceR
   [Blue]SET[/Blue] @TotalM[Gray]=[/Gray]@TotalM[Gray]+[/Gray]@DiscountPriceM
[Blue]END[/Blue]
[Blue]SELECT[/Blue] @DiscountPriceR[Gray],[/Gray]@TotalR[Gray],[/Gray]@DiscountPriceM[Gray],[/Gray]@TotalM

The real problem isn't an exact datatype...there isn't one when you work with decimals. It's when you choose to do the rounding. I'd like to see an example of how a real datatype is the cause of the problem. My contention is that the money datatype forces rounding that may help reduce the accumulation of rounding errors in real-world financial calculations, but it clearly doesn't eliminate them (above code).
The informed 100% solution is to know when to round.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Division is always tricky because of great total differential. Especially if divisor is small:

declare @r real
set @r = 1./42; select 1./@r
set @r = 1./4200; select 1./@r
set @r = 1./420000; select 1./@r

That was for data type with floor(log(power(2, 24))/log(10)) = 7 accurate digits. I won't even try to run the same code with money type (4 accurate decimals)

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Well, I'm on the again. I guess I should answer the question of when to round.

[Blue]DECLARE[/Blue] @Cnt [Blue]int[/Blue]
[Blue]DECLARE[/Blue] @RetailPrice [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @DiscountReal [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @DiscountMoney [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @DiscountPriceR [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @DiscountPriceM [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @TotalR [Blue]real[/Blue]
[Blue]DECLARE[/Blue] @TotalM [Blue]money[/Blue]
[Blue]SET[/Blue] @TotalR[Gray]=[/Gray]0
[Blue]SET[/Blue] @TotalM[Gray]=[/Gray]0
[Blue]SET[/Blue] @RetailPrice[Gray]=[/Gray]1.00
[Blue]SET[/Blue] @DiscountReal[Gray]=[/Gray]1./3
[Blue]SET[/Blue] @DiscountMoney[Gray]=[/Gray]1./3
[Blue]SET[/Blue] @DiscountPriceR[Gray]=[/Gray][Fuchsia]Round[/Fuchsia][Gray]([/Gray][Gray]([/Gray]1[Gray]-[/Gray]@DiscountReal[Gray])[/Gray][Gray]*[/Gray]@RetailPrice[Gray],[/Gray]2[Gray])[/Gray]
[Blue]SET[/Blue] @DiscountPriceM[Gray]=[/Gray][Fuchsia]Round[/Fuchsia][Gray]([/Gray][Gray]([/Gray]1[Gray]-[/Gray]@DiscountMoney[Gray])[/Gray][Gray]*[/Gray]@RetailPrice[Gray],[/Gray]2[Gray])[/Gray]
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]0
[Blue]WHILE[/Blue] @Cnt[Gray]<[/Gray]100 [Blue]BEGIN[/Blue]
   [Blue]SET[/Blue] @Cnt[Gray]=[/Gray]@Cnt[Gray]+[/Gray]1
   [Blue]SET[/Blue] @TotalR[Gray]=[/Gray][Fuchsia]Round[/Fuchsia][Gray]([/Gray]@TotalR[Gray]+[/Gray]@DiscountPriceR[Gray],[/Gray]2[Gray])[/Gray]
   [Blue]SET[/Blue] @TotalM[Gray]=[/Gray]@TotalM[Gray]+[/Gray]@DiscountPriceM
[Blue]END[/Blue]
[Blue]SELECT[/Blue] @DiscountPriceR[Gray],[/Gray]@TotalR[Gray],[/Gray]@DiscountPriceM[Gray],[/Gray]@TotalM

This demonstrates why the money datatype is prefered. It forces a rounding at 4 decimals which you can count on as sufficient when the starting value is dollars and cents only. With the real datatype you should round the result after each calculation. Remove the rounding in the loop and perform the loop 1,500 times and you will get the real datatype to fail. The money datatype still succeeds because of the forced intermediate rounding at 4 decimals, but it does still require an initial rounding after the multiplication. Know when to round!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

And 33 cents went bye-bye, right? I want my money back!

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Didn't some programmer get put in jail for siphoning off the roundings at a bank?

[Blue]DECLARE[/Blue] @r [Blue]decimal[/Blue][Gray]([/Gray]38[Gray],[/Gray]30[Gray])[/Gray]
[Blue]SET[/Blue] @r [Gray]=[/Gray] 1./42; [Blue]SELECT[/Blue] .0000001/@r[Gray]*[/Gray]10000000
[Blue]SET[/Blue] @r [Gray]=[/Gray] 1./4200; [Blue]SELECT[/Blue] [Gray]([/Gray].0000000001/@r[Gray])[/Gray][Gray]*[/Gray]10e9
[Blue]SET[/Blue] @r [Gray]=[/Gray] 1./420000; [Blue]SELECT[/Blue] [Gray]([/Gray].0000000000001/@r[Gray])[/Gray][Gray]*[/Gray]10e12

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

It's funny... I was coming here to make the post learn about float data types and just happened to read this thread first--the only one I planned to read before posting.

 

[donutman]

Before I read about floating point numbers, I have a somewhat humorous story about banks, programmers and when to round (actually in this case it was when to eliminate negatives).
About 30 years ago, banks started offering "cash reserves" aka check protection. I availed myself of it and soon discovered that the high interest rate that they charged worked in reverse between statement dates. You could never actually earn interest, but you could reduce your cost of funds accrued during the month. Since at that time I was young and naive and therefore was unaware of the fact that the stock market was just legalized gambling I would borrow against the account for 3 weeks and pay back 3 times as much for 1 week which also happened to be about the length of the float at the time. I kept it legal by always having sufficient funds to cover the big check, but the float allowed me to "earn" interest or play the market at both ends.
After a few months I got bored with the mechanics and told them about their program error. The programmer didn't eliminate negative interests on a daily basis. You have to know when to round!
ESquared on the topic of coincidence, have you read, The Philistine Prophecy? The older I get, the more I'm beginning to believe that we are all connected...it's so bizarre how bakery product preferences for holidays are nation wide and unrelated to economics. One year everyone is buying fondant heart shapped cakes for Valentines Day instead of whipped cream heart shapped tortes...the next year it goes the other way. But then again, the older I get, the more senile I get!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I don't think the answer is rounding, at all. Read the seven articles in the above link, including the comments after each one.

 

[donutman]

I don't think the answer is rounding, at all.

Not at all, really! Maybe you're splitting hairs? I think of rounding errors as encompassing what Eric Lippert calls representation error. But actually, I wasn't looking for an answer anyway. I'm kind of a practical guy. I've always found that rounding solved the problems that SQLSister alludes to when using floats (and the money datatype). I was looking for a solution and rounding does it. However, I'm also a curious guy. I've read them all, but not the comments. It took me 10 minutes to figure out how
1.0110000000000000000000000000000000000000000000000000 = 1.375 so the rest of it wasn't entirely clear. It sure is nice that there are people who understand this stuff so that a mere mortal can have a computer that works...sort of.
So being curious, what is the answer and more importantly which question are you answering? Are you referring to my statement that the reason why the money datatype worked in the loop is not because of intermediate step rounding, but because of some other reason?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

In your example loop, you apply the discount to an amount 100 times. I think the best answer in this case (which admittedly was manufactured, but still) is to do all the addition first, and apply the discount in one operation which would have more than enough precision with either decimal type to get dollars and cents. The cure for this particular rounding error you showed is to change how and when the implicit rounding occurs rather than mess with the data type and explicit rounding.

One part of the series talked about how commutativity is out the window with floats. For example:

(big number + small number) + small number
can give a different answer than
big number + (small number + small number)

And the same thing is true no matter what the operation.

I shouldn't have put the words "at all" in there: I didn't mean to say that explicit rounding has no place in handling accumulated implicit rounding errors. Explicit rounding can definitely be a useful tool! I was more thinking about how the problem should first be addressed from a reprogramming perspective and that focusing immediately on rounding could be premature.

"How can I restructure the process so implicit float data type rounding errors are not accumulated?

 

[donutman]

I think the best answer in this case (which admittedly was manufactured, but still) is to do all the addition first, and apply the discount in one operation which would have more than enough precision with either decimal type to get dollars and cents.

No, the example that I created is typical of an invoicing system where various discount levels are applied to individual products. The invoice total is the sum of the individual discounts. An inexperienced programmer might not round the individual items before they are summed.
The big-number-small-number commutative property issue you referred to has nothing, at all (hehe), to do with the summation errors for financial calculations (unless you're talking about GDP figures for the USA) which might exceed the number of significant digits retained. They could come into play when when you start performing multiplications & summations e.g. in compound interest present value calculations of an annuity or mortgage loan principal and interest calculations. But here again the correct way is to compute the particular years value of interest and principal payment and round them before summing and then go onto the subsequent years calculation. The financial formulas that compute the theoretical total of interest payments will almost always produce an incorrect result.

"How can I restructure the process so implicit float data type rounding errors are not accumulated?"

I agree 100%, when working with scientific oriented projects. I still contend it's 100% rounding (w/representation error included) when working with company financial data (ok maybe not MS ).
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Okay, I agree that rounding is very important in financial scenarios... one can only charge whole dollars and cents and it would be a mistake to, for example, charge late fees for some amount less than one cent. So, rounding has to happen whenever the program wishes to interact with the real world or give final output for human consumption.

And I see your point about rounding, but I don't know that it totally applies. This is exactly like calculating sales tax. How many times have I broken out a receipt in my financial tracking and discovered that I was "overcharged" or "undercharged" by one cent? Many times.

That is, by taking the dollars and whole cents value of the price + tax for each individual item, and summing them, I end up with a different amount than the total actually charged me. At the end of the year, the government wants the store to pay the tax based on the total amount, not based on summing the tax for each product sold. Doing it for the whole receipt will result in lower potential accumulated rounding error. And Benford's law implies that rounding error will tend to accumulate in one direction rather then the other, also dependent on the base used for the float calculations.

So, for your scenario, if all the items being calculated will be billed on one receipt, then I think the rounding should wait until the final step, and using a float value without rounding in the loop is quite appropriate. I mean, why not?

I recently refinanced my car. While I was figuring out the loan I had, though, I put it all into an Excel spreadsheet and fiddled for a few hours until I'd figured out the exact method used by the financial institution to calculate interest. What I found was that they rounded to 4 decimal places at one point, and didn't round at all at another point, and then finally rounded to dollars and cents only at the final moment. (I know this because I managed to duplicated their exact calculated balances based on dates payments posted, and so on.)

So well, I think I'm finding that I agree with you: knowing when to round and not to round is crucial, but I wouldn't say that rounding itself is always required or beneficial.

Oh, and my long distance phone company bills me by the second, and posts tenths of a cent on the final bill! I really like it, it appeals to me a lot. And supposedly "You saved an extra 20% thanks to one-second billing!" That would mean $7.32 I saved. So there's another reason I'm not convinced that rounding is the final solution.

 

[donutman]

You're right, it's not always required or beneficial. But do remember, an apple a day keeps the doctor away.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]