Well, I haven't tested this thoroughly, but I think this will work:
DECLARE @modulus float, @exponent int
SET @modulus = 0.25
SET @exponent = 0.0
WHILE (@modulus / 1.0) <> CAST(@modulus as int)
BEGIN
SET @modulus = @modulus * 10
SET @exponent = @exponent + 1
END
SELECT CAST(discount * POWER(10.0,@exponent) AS int) FROM pubs..discounts
SELECT CAST(@modulus AS int)
SELECT discount * POWER(10.0,@exponent) / @modulus AS discount
FROM pubs..discounts
SELECT discounttype, discount, FLOOR(discount * POWER(10.0,@exponent) /
@modulus) * @modulus / POWER(10.0,@exponent) AS newdiscount
FROM pubs..discounts
You could easily turn this into a stored proc and pass in the decimal
value as a parameter. This is a variation on standard rounding algorithms
that I use in languages without a ROUND function. The first three SELECT
statements are there just so you can see a little bit of how the query
works. You can remove them and just stick with the last one if you want.
Since the query uses the FLOOR function, it will always round down. You
can substitute the CEILING or ROUND functions if you want to always round
up or round according to an arbitrary precision.
Here is my sample run, using the pubs database as shown above (@modulus =
0.25):
discounttype discount newdiscount
----------------------- -------- -----------
Initial Customer 10.50 10.5
Volume Discount 6.70 6.5
Customer Discount 5.00 5.0
Thanks,
Eric
"Susan McNab" <[EMAIL PROTECTED]>
10/23/2003 11:11 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Limiting values in decimal increments?
I can't believe I am unable to think of a nice neat way to do this.
Does anyone know of a way to limit a value to a specific decimal increment
in MSSQL Server 2000? I have a trigger that calculates and saves a result
upon insert and update. I want to apply a rule to the calculation that
limits the multiplier to a multiple of a decimal value. This would be
supplied
@Any_Val_1 * @Any_Val_2 = @Result_Val
The increment needs to be variable depending on the situation. (Might be
0.25 at one time, 0.5 another. 1.0 is easy of course). How can I round up,
or down, Any_Val_2 to enforce a rule that it must be evenly divisible by a
specified decimal value?
I am stumped. It's driving me crackers. I would really appeciate it if
anyone has a good idea for this type of problem. Everything I can think of
this morning looks like a gruesome, convoluted, resource-wasting case
monster.
Thanks
Sue
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the mailserver that powers this list at
http://www.coolfusion.com
Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1705
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
