Round up to the nearest 500 or 1000 in SQL Server

Question

Possible Duplicate:
Round UP to the nearest 100 in SQL Server

Is it possible to round up a figure to the nearest 500 or 1000 in SQL Server?

Example:

14425.00 -> 14500.00
14585.00 -> 15000.00

Answer 1

I'd use this, with data by example

declare @num int
set @num = 749
select (round(((@num+250)/500),0)*500)
Result = 500

or to show it works

declare @num int
set @num = 750
select (round(((@num+250)/500),0)*500)
Result = 1000

however wrapping into a general function would be sensible

CREATE FUNCTION ufnRoundMyValue 
(
    @val int,
    @base int
)
RETURNS int
AS
BEGIN
    declare @res int

    select @res = (round(((@val+(@base/2))/@base),0)*@base) 
    RETURN @res
END
GO

then it's just

select dbo.ufnRoundMyValue(749,500)

wherever you need it

Answer 2

Always rounding up:

((value+499)/500)*500

Rounding up or down:

((value+250)/500)*500

Answer 3

 SELECT (((col+250)/500)*500) FROM table;