Round Natural Numbers to the nearest Power of 10

,

The function requires 3 parameters:

  1. Natural Number
  2. Nearest Power of 10
  3. Direction

Simple Use Cases:

SELECT * FROM dbo.udf_RoundNearest (1264,10,0) /* The Direction 0 indicates the nearest Power of 10 round down. Result = 1260 */SELECT * FROM dbo.udf_RoundNearest (126412,1000,0) /* Result 126000*/SELECT * FROM dbo.udf_RoundNearest (126412,1000,1) /* Result 127000*/

Integrating the function with a table/view/derived table/CTE:

;with tmp (Value) as
  (select 1236 union all select 6584 union all select 9999)
    select t.*, fn.Number
    from tmp t
    cross apply dbo.udf_RoundNearest (Value,100,0) fn
/* Value Number
   1236 1200
   6584 6500
   9999 9900 */

CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint, /*Natural Number*/@RoundNearest bigint, /*Power of 10, 10^n , 10,100,1000...*/@Direction int /* 0-> Down , 1 -> Up */)

RETURNS TABLE AS 
RETURN

SELECT ISNULL(CASE WHEN @RoundNearest>=@Number THEN @Number
   ELSE
(
(@Number + CASE 
   WHEN @Direction = 0
   THEN 0
   ELSE CASE WHEN @Number % @RoundNearest = 0 THEN 0 ELSE @RoundNearest END
   END) / NULLIF(@RoundNearest,0)) * @RoundNearest

   END , @Number) Number

Rate

Share

Share

Rate