Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Round nearest 10

Posted on 2013-10-28
8
Medium Priority
?
474 Views
Last Modified: 2013-10-29
Dear Experts,
I have a value 185 which i need to convert round nearest 10. the answer will be 190. please help me sql.
Rgds.
Iqbal
0
Comment
Question by:Mehram
8 Comments
 
LVL 49

Expert Comment

by:PortletPaul
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 |

Open in new window

0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 39607784
You may use

ROUND(<NUM>,-1)
0
 
LVL 49

Expert Comment

by:PortletPaul
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 |

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:Mehram
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

by:PortletPaul
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
;

Open in new window

0
 
LVL 4

Accepted Solution

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

Expert Comment

by:PortletPaul
ID: 39607838
:)
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39607839
Hello,

Please check below code

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

Open in new window


Best Regards,
Mohit Pandit
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question