Solved

Round nearest 10

Posted on 2013-10-28
8
421 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 48

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 48

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 48

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 500 total points
ID: 39607821
Hi
  Select  round(<NUM>-1 ,-2)
0
 
LVL 48

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
sql query 8 51
Generate Scripts of Schema/Data with "WHERE" clause 6 72
Addition to SQL for dynamic fields 6 47
get column names from table in vb.net 8 49
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 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