sqlcurious
asked on
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
original result-expected result
21937.5 - 22000
3528 - 3500
4904.25 - 5000
17154.75 -17000
25938.75 - 26000
2209.5 - 2000
2760- 3000
select round(@number/ 500) * 500
Try:
Round(x/1000)*1000
Round(x/1000)*1000
You can round to even 100 like so:
(CAST(ROUND(MAX(DISTINCT i.invoice_amt), -2) AS INT))
(CAST(ROUND(MAX(DISTINCT i.invoice_amt), -2) AS INT))
Now we "just" have to go from 100s to 500s :-) .
Duh, that won't work, but Round(x/500)*500 works fine on my Access system.
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))
(CAST((MAX(DISTINCT i.invoice_amt) + 250.0) / 500.0 * 500.0 AS INT))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
ASKER
SELECT ROUND ( column/500 , 0, 0)*500