return all the value between two decimal value

Question

i have two values which has decimal(15,2) datatypes

eg.

@from='19.99'

and

 @to='20.02'

i have to return the range between that two value

expected output-19.99, 20.00, 20.01, 20.02

Answer 1

you can use Tally/Number approach on this like below

DECLARE @from DECIMAL(10,2) , @to DECIMAL(10,2), @interval DECIMAL(10,2)

SET @from=19.99
SET @to= 20.02
SET @interval=0.01
SELECT @from +r*@interval 
FROM
(
SELECT TOP (SELECT CAST((@to-@from)/@interval AS INT)+1)
ROW_NUMBER() OVER( ORDER BY (SELECT 1)) -1  r
FROM 
sys.objects s1 CROSS JOIN sys.objects s2
)T

Working demo

Answer 2

Try:

DECLARE @From DECIMAL (15,2) = 19.99;
DECLARE @To DECIMAL (15,2) = 20.02;
DECLARE @Step DECIMAL (15,2) = 00.01;
DECLARE @R TABLE (Value DECIMAL(15,2));

WHILE @From <= @To
    begin
        INSERT INTO @R VALUES (@From);
        SET @From = @From + @Step;
    End
SELECT *
FROM @R;

Result:

+=======+
| Value |
+=======+
| 19,99 |
+-------+
| 20,00 |
+-------+
| 20,01 |
+-------+
| 20,02 |
+-------+

Using a CTE:

DECLARE @From DECIMAL (15,2) = 19.99;
DECLARE @To DECIMAL (15,2) = 20.02;
DECLARE @Step DECIMAL (15,2) = 00.01;

WITH CTE AS(
    SELECT @Step * 1 Val
        UNION ALL
    SELECT @Step * 2
        UNION ALL
    SELECT @Step * 3
        UNION ALL
    SELECT @Step * 4
    )
  SELECT @From - @Step + CTE.Val Value FROM CTE;

Result:

+=======+
| Value |
+=======+
| 19,99 |
+-------+
| 20,00 |
+-------+
| 20,01 |
+-------+
| 20,02 |
+-------+

Answer 3

You can use a recursive CTE:

with cte as (
      select @from as val
      union all
      select cast(val + 0.01 as decimal(15,2))
      from cte
      where density < @to
     )
select val
from cte
option (MAXRECURSION 0);

Note: If you have more than 100 rows to insert, you will need to explore the max recursion option.

You can also do something similar with a "numbers" table.