rounding the result to near 500's -SQL

Hi experts I have column which gives the following results(CAST(MAX(DISTINCT i.invoice_amt) AS INT)) and I was asked to round it to the nearest 500, was wondering how I could achieve that, please help:
original result-expected result
21937.5 - 22000
3528 - 3500
4904.25 - 5000
17154.75 -17000
25938.75 - 26000
2209.5 - 2000
2760- 3000
sqlcuriousAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
deightonConnect With a Mentor Commented:
SELECT ROUND ( column/500.0 , 0, 0)*500

just put the 500.0 in the divisor to make it do the calc as a float
0
 
sqlcuriousAuthor Commented:
I tried the below and it works for most of the numbers but doesnt for 2760. For 2760 I am getting 2500 but would need 3000 as a result, please help

     
SELECT ROUND ( column/500 , 0, 0)*500
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select round(@number/ 500) * 500
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Dale FyeCommented:
Try:

Round(x/1000)*1000
0
 
Scott PletcherSenior DBACommented:
You can round to even 100 like so:

(CAST(ROUND(MAX(DISTINCT i.invoice_amt), -2) AS INT))
0
 
Scott PletcherSenior DBACommented:
Now we "just" have to go from 100s to 500s :-) .
0
 
Dale FyeCommented:
Duh, that won't work, but Round(x/500)*500 works fine on my Access system.
0
 
Scott PletcherSenior DBACommented:
Never mind.  Hopefully this will do the whole thing in one shot

(CAST((MAX(DISTINCT i.invoice_amt) + 250.0) / 500.0 * 500.0 AS INT))
0
 
sqlcuriousAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.