How to generate decimal numbers sequence for particular number range

Question

Consider following example for above question. Suppose we have series of decimal numbers like (12.50 ,13.20 etc.) I want result as

12.51001
12.51002
....
13.19999
13.20000

Upto 5 decimal places it to be generated. Is it possible in sql ?

Answer 1

Here is one trick using Recursive CTE

Cast your data to 5 decimal places in CTE to get the result in required format

;WITH cte
     AS (SELECT Cast(12.50 AS NUMERIC(22, 5)) AS num --Min value from your data
         UNION ALL
         SELECT Cast(num + 0.00001 AS NUMERIC(22, 5))
         FROM   cte
         WHERE  num < Cast(13.20 AS NUMERIC(22, 5))) -- Max value from your data
SELECT *
FROM   cte
OPTION (maxrecursion 0) 

In your expected result, Data starts from 12.51001 though your sample data starts from 12.50. If you really need to start from 12.51001 then add 0.01001 to the source query of CTE

 Cast(12.50 +0.01001 AS NUMERIC(22, 5)) AS num

Answer 2

use a number table or recursive cte

; with rcte as
(
    select  n = 1250000
    union all
    select  n = n + 1
    from    rcte
    where   n   < 1320000
)
select  convert(decimal(10,5), n / 100000.0)
from    rcte
option (maxrecursion 0)

Answer 3

DECLARE @startnum decimal(18,5)=12.50
DECLARE @endnum decimal(18,5)=13.20

set @startnum = @startnum+0.01

 ;WITH cte
 AS (SELECT Cast(@startnum AS NUMERIC(22, 5)) AS num
     UNION ALL
     SELECT Cast(num + 0.00001 AS NUMERIC(22, 5))
     FROM   cte
     WHERE  num < Cast(@endnum AS NUMERIC(22, 5)))
 SELECT *
 FROM   cte
 OPTION (maxrecursion 0) 

Answer 4

I would suggest not using a loop to generate your sequence, instead use a tally table. Aaron Bertrand has tested the various methods of generating a set and it comfortably out performs the recursive CTE. In fact, the recursive CTE is so bad it is removed from most of the results because it distorts the scale on the graphs by so much.

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

So you could using something like:

DECLARE @StartNumber DECIMAL(10, 5) = 12.50,
        @EndNumber DECIMAL(10, 5) = 13.20,
        @Increment DECIMAL(10, 5) = 0.00001;

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT 1 FROM N3 AS N1 CROSS JOIN N3 AS N2)

SELECT TOP (1 + CONVERT(INT, CEILING((@EndNumber - @StartNumber) / @Increment)))
        @StartNumber + ((ROW_NUMBER() OVER(ORDER BY N) - 1) * @Increment)
FROM    N4;

As a quick benchmark on your requirement, if I change the end number up to 23.2 this consistently takes about 4 seconds to run on my machine, the recursive CTE takes about 10 seconds to produce the same set.