Solved

Round nearest 10

Posted on 2013-10-28
8
416 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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