Numeric Rounding - 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)

Numeric Rounding

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-05-23 : 14:48:51

[code]
Declare @Completed Table
(
Started Numeric(5,1),
Completed Numeric(5,1)
)
Insert into @completed
Select 6,4
union all
select 5,4
union all
select 1,1

Select Convert(Numeric(5,1),(Completed / started )*100) from @completed

Result is:
66.7
80.0
100.0
[/code]
What I want is :
66.7
80
100

One option I thought about is convert the result to varchar then replace ".0" with empty string. The result will be passed to the dataset in asp.net i guess that shouldnt be a problem. But is there any other way to achieve this?

Thanks

Karunakaran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 14:53:16

We can't use the ROUND functions here as what you want isn't rounding. Converting it to varchar then performing a replace sounds fine, but you should handle this in your presentation layer (your application) rather than in T-SQL.

Tara Kizer
aka tduggan

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-05-23 : 15:10:41

I can do that in presentation layer, but that would mean code change which I dont want to do at the current stage of my development.

Looks like there is no other option so I have to go with the replace method.

Thanks for the reply Tara.

Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 05:49:15

I agree with Tara that this should be done in the presentation layer, but if you're going to use the replace method, you might want to try something like this as an alternative...

I've no idea if it will perform any better, but if you do some tests, please let us know

--data
Declare @Completed Table (Started Numeric(5,1), Completed Numeric(5,1))
Insert into @completed
          Select 6,4
union all select 5,4
union all select 1,1

--calculation
select case when x = round(x, 0) then cast(cast(x as int) as sql_variant) else x end
from (Select Convert(Numeric(5,1),(Completed / started )*100) as x from @completed) a

--result
66.7
80
100

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-05-24 : 07:55:34

quote:
Originally posted by RyanRandall

I've no idea if it will perform any better, but if you do some tests, please let us know

Performance is not a problem even if it takes little more time than replace then also fine with me. Some how Replace method did not interest me.

Thanks for the solution.

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources