round up to nearest .05 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

round up to nearest .05

Author

Topic

tokson
Starting Member

2 Posts

Posted - 2005-01-24 : 02:26:51

HI
I want to update my SELLINGPRICE field according to a formula SELLINGPRICE = SELLINGPRICE +(STDCOST*10/100) and result should round up to nearest .05 fils.

for example
if the result of formula is
10.1 then 10.5
10.5 then 10.5 or
10.6 then 11.0

normal round function doesn't work.
anyone can help me?

Thanks in advance
Tokson

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-24 : 11:19:01

tokson,
Here's one option:


If Object_ID('dbo.RoundUpPnt5') > 0
	drop function dbo.RoundUpPnt5
GO
create function dbo.RoundUpPnt5(@num float)
returns numeric(15,1)
as
Begin
	declare @retVal numeric(15,1)
	
	SElect @retVal = floor(@num) + 
		case 
		when @num - floor(@num) = 0 then 0
		when @num - floor(@num) < .5 then .5
		else 1
		End
	
	return @retVal
End
GO


SElect 	dbo.RoundUpPnt5(10.1) [10.1]
	,dbo.RoundUpPnt5(10.5) [10.5]
	,dbo.RoundUpPnt5(10.6) [10.6]

/*
SElect	Sellingprice = dbo.RoundUpPnt5( SELLINGPRICE +(STDCOST*10/100) )
From	...
*/

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-01-24 : 17:40:47

Basically rephrasing here a reply from SQL Server MVP Adam Machanic on the MS newsgroups. See if this helps:


declare @myvalue float
set @myvalue = 10.6

select convert(int, @myvalue) +
 case
 when convert(int, round((@myvalue - convert(int, @myvalue)) * 100, 0))
between 0 and 50 then 0.5
 when convert(int, round((@myvalue - convert(int, @myvalue)) * 100, 0))
between 51 and 99 then 1
 else 1.0 end

--
Frank
http://www.insidesql.de

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-24 : 18:18:41

Just for fun...

no case statement:


Declare @a money
Set @a = 10.2

Select (convert(int,@a*10)/5 + round(convert(money,convert(int,@a*10)%5)/5+.49,0))*.5

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-24 : 18:27:28

oh and the title specified .05

so you only need to boost the 10(s) to 100(s):


Declare @a money,
	@digits int
Set @a = 10.02
Set @digits = 2

Select (convert(int,@a*power(10,@digits))/5 + round(convert(money,convert(int,@a*power(10,@digits))%5)/5+.49,0))*(5.0/power(10,@digits))

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

tokson
Starting Member

2 Posts

Posted - 2005-01-25 : 01:25:08

HI all

Thanks. All options are working fine!!!

Tokson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources