Solved

Round nearest 10

Posted on 2013-10-28
8
423 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
[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
  • Learn & ask questions
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 31
sql 2016 data tools breakdown.. 1 27
SSIS Standard Template for Reuse by Business Units 12 49
Need multiple Group By's 8 28
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …

733 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