Solved

Round nearest 10

Posted on 2013-10-28
443 Views
Dear Experts,
Rgds.
Iqbal
0
Question by:Mehram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 49

Expert Comment

ID: 39607781
here's a way
select
( (180 + 5) /10)*10 as i0
, ( (181 + 5) /10)*10 as i1
, ( (182 + 5) /10)*10 as i2
, ( (183 + 5) /10)*10 as i3
, ( (184 + 5) /10)*10 as i4
, ( (185 + 5) /10)*10 as i5
, ( (186 + 5) /10)*10 as i6
, ( (187 + 5) /10)*10 as i7
, ( (188 + 5) /10)*10 as i8
, ( (189 + 5) /10)*10 as i9
, ( (190 + 5) /10)*10 as i10

Results

|  I0 |  I1 |  I2 |  I3 |  I4 |  I5 |  I6 |  I7 |  I8 |  I9 | I10 |
|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| 180 | 180 | 180 | 180 | 180 | 190 | 190 | 190 | 190 | 190 | 190 |
0

LVL 16

Expert Comment

ID: 39607784
You may use

ROUND(<NUM>,-1)
0

LVL 49

Expert Comment

ID: 39607790
here's another:
select
round(180/10.0,0) * 10 as i0
, round(181/10.0,0) * 10 as i1
, round(182/10.0,0) * 10 as i2
, round(183/10.0,0) * 10 as i3
, round(184/10.0,0) * 10 as i4
, round(185/10.0,0) * 10 as i5
, round(186/10.0,0) * 10 as i6
, round(187/10.0,0) * 10 as i7
, round(188/10.0,0) * 10 as i8
, round(189/10.0,0) * 10 as i9
, round(190/10.0,0) * 10 as i10

**[Results][2]**:

|  I0 |  I1 |  I2 |  I3 |  I4 |  I5 |  I6 |  I7 |  I8 |  I9 | I10 |
|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| 180 | 180 | 180 | 180 | 180 | 190 | 190 | 190 | 190 | 190 | 190 |
0

Author Comment

ID: 39607791
select round(185 ,-1)

result 190

How can change it sir into nearest 100 if value is > 50 other wise not change.
0

LVL 49

Expert Comment

ID: 39607803
by using a case expression:
declare @n as int
set @n = 185

select
case when @n > 50 then round(@n,-2) else @n end
;

declare @n as int
set @n = 50

select
case when @n > 50 then round(@n,-2) else @n end
;
0

LVL 4

Accepted Solution

rshq earned 500 total points
ID: 39607821
Hi
Select  round(<NUM>-1 ,-2)
0

LVL 49

Expert Comment

ID: 39607838
:)
0

LVL 5

Expert Comment

ID: 39607839
Hello,

DECLARE @Val Int = 185
SELECT
nearValue =
CASE
WHEN @Val % 10 <> 0 -- remainder
THEN @Val + (10-(@Val % 10))
ELSE @Val
END

Best Regards,
Mohit Pandit
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month1 day, 14 hours left to enroll