Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

Round nearest 10

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
Mehram
Asked:
Mehram
1 Solution
 
PortletPaulCommented:
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
 
Peter KwanCommented:
You may use

ROUND(<NUM>,-1)
0
 
PortletPaulCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
MehramAuthor Commented:
select round(185 ,-1)

result 190

How can change it sir into nearest 100 if value is > 50 other wise not change.
0
 
PortletPaulCommented:
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
 
rshqCommented:
Hi
  Select  round(<NUM>-1 ,-2)
0
 
PortletPaulCommented:
:)
0
 
MohitPanditCommented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now