EXPERT RESPONSE
This can accomplished with the SQL Server CEILING function.
select mynumber
, mynumber/1000.0 as dividedby1000
, ceiling(mynumber/1000.0) as ceilingresult
, ceiling(mynumber/1000.0)*1000 as myfinalnumber
from mynumbers
CEILING returns the smallest integer which is equal
to or greater than the number it is given. Notice that we need a complex
expression involving CEILING, in order to get the "rounded up to next 1000"
number that you wanted. The "trick" (if you want to call it that) is to
divide by 1000.0, which forces a decimal result, before
applying the CEILING.
The query above also illustrates how to go about testing a complex
expression like this. Build it up, one step at a time, in separate
columns.
The results of the query on the data you provided are:
mynumber dividedby1000 ceilingresult myfinalnumber
460 0.460000 1 1000
1000 1.000000 1 1000
1001 1.001000 2 2000
1550 1.550000 2 2000
2001 2.001000 3 3000
Neat, eh?
|
