April 2, 2009 at 3:09 pm
Hello Everyone,
I have some problem in table to round up some values.
Example 1. value(declared as money) is 0.1234 base on 0.001 round up, final value will be 0.13 regardless, whatever0.xx3 is bigger then 5 or not, value must round to 0.13.
Example 2. value(declared as money) is 0.1234 base on 0.005 round up, final value will be 0.15 regardless, whatever0.xx3 is bigger then 5 or not, value must round to 0.15.
I tried round(xxx,2) function but it doesn't work. please help.
April 2, 2009 at 3:46 pm
I've always found it easier to understand/read to use the FLOOR and CEILING functions when specific rules are required when shortening numbers with specific rules. If necessary add a factor to the base number to ensure that the right result is achieved, but FLOOR or CEILING (in the case you're describing it sounds like CEILING) generally do the trick, as follows:
declare @val money
set @val = 0.1200
select ceiling(@Val * 100) / 100
-- result is 0.12
set @val = 0.1201
select ceiling(@Val * 100) / 100
-- result is 0.13
It's not pretty but it does the job.
April 4, 2009 at 9:11 am
I too had the same requirement in one of our application but I didn't find any useful scripts at that time. So, I decided to write my own function for that.
Here is that function:
/*-------------------------------------------------------------------------------------------------
Author:Ramesh Saive
Created On:28th February, 2007
Purpose:To round the input value to specified decimals
Input Parameters :1. @nValue: Value
2. @strRoundType: Round Type [G: Greatest/Round Up, N: Nearest, L: Lowest/Round Down]
3. @nRoundedTo: Rounded To
Output Parameters:1. Rounded Value
Resultsets:None
Example:SELECT dbo.fnRound( 0.1234, 'G', 0.01 )
SELECT dbo.fnRound( 0.1234, 'N', 0.01 )
SELECT dbo.fnRound( 0.1234, 'L', 0.01 )
Revisions:None
-------------------------------------------------------------------------------------------------*/
ALTER FUNCTION [dbo].[fnRound]
(
@nValueNUMERIC(18,4),
@strRoundType CHAR(1),
@nRoundedToNUMERIC(9,4)
)
RETURNS NUMERIC(18,4)
AS
BEGIN
DECLARE @nRoundedValue NUMERIC(18,4),
@siToPlacesSMALLINT,
@nReminder NUMERIC(18,4)
SELECT@nRoundedValue = @nValue,
@nReminder = @nValue % @nRoundedTo
SELECT@siToPlaces= (CASE @nRoundedTo
WHEN10000THEN -4
WHEN1000THEN -3
WHEN100THEN -2
WHEN10THEN -1
WHEN1THEN 0
WHEN0.1THEN 1
WHEN0.01THEN 2
WHEN0.001THEN 3
WHEN0.0001THEN 4
ELSE0
END )
IF ( @strRoundType = 'G' )
BEGIN
SET @nRoundedValue = ( CASE WHEN @nReminder > 0 THEN @nRoundedTo + @nValue ELSE @nValue END ) - @nReminder
END
ELSE IF ( @strRoundType = 'N' )
BEGIN
SET @nRoundedValue = ROUND( @nValue, @siToPlaces )
END
ELSE IF ( @strRoundType = 'L' )
BEGIN
SET @nRoundedValue= @nValue - @nReminder
END
RETURN @nRoundedValue
END
--Ramesh
April 4, 2009 at 11:43 am
Leo (4/2/2009)
Hello Everyone,I have some problem in table to round up some values.
Example 1. value(declared as money) is 0.1234 base on 0.001 round up, final value will be 0.13 regardless, whatever0.xx3 is bigger then 5 or not, value must round to 0.13.
Example 2. value(declared as money) is 0.1234 base on 0.005 round up, final value will be 0.15 regardless, whatever0.xx3 is bigger then 5 or not, value must round to 0.15.
I tried round(xxx,2) function but it doesn't work. please help.
Leo... look up in Books Online to see what the 3rd operand of ROUND does in the following examples...
[font="Courier New"]
DECLARE @SomeMoney MONEY
SELECT @SomeMoney = 0.1234
SELECT ROUND(@SomeMoney+.009,2,1)
SELECT @SomeMoney = 0.1200
SELECT ROUND(@SomeMoney+.009,2,1)
SELECT @SomeMoney = 0.1204
SELECT ROUND(@SomeMoney+.009,2,1)
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply