Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

rounding the result to near 500's -SQL

Posted on 2012-03-19
9
Medium Priority
?
306 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:sqlcurious
[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
  • 3
  • 2
  • 2
  • +2
9 Comments
 

Author Comment

by:sqlcurious
ID: 37739510
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37739558
select round(@number/ 500) * 500
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37739559
Try:

Round(x/1000)*1000
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37739576
You can round to even 100 like so:

(CAST(ROUND(MAX(DISTINCT i.invoice_amt), -2) AS INT))
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37739581
Now we "just" have to go from 100s to 500s :-) .
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37739582
Duh, that won't work, but Round(x/500)*500 works fine on my Access system.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37739604
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
 
LVL 18

Accepted Solution

by:
deighton earned 2000 total points
ID: 37742738
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
 

Author Closing Comment

by:sqlcurious
ID: 37778749
thanks
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

730 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