Technical Article

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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

External Article

What are Inline Indexes?

New releases of SQL Server arrive at a quick pace, and it's difficult to keep up with the many features introduced in each version. In this article, Phil Factor reviews a feature you may have missed, inline indexes. He covers the syntax and the many ways they can be used and then performs some performance tests to see if they can make a difference with table variables.

2019-03-18

3,669 reads