How to check the current row with next row and update the current row not using while loop and cursor

Is there any way other than using the cursor or while loop to check the current row with the next row and update the current row and do this through all rows in the data set?

For example, this is the table we have:

id  date                      value
1   2020-09-01 00:00:00.000   0.00
2   2020-09-01 00:15:00.000   0.30
3   2020-09-01 00:30:00.000   0.00
4   2020-09-01 00:45:00.000   0.15
5   2020-09-01 01:00:00.000   0.20
6   2020-09-01 01:15:00.000   0.10
7   2020-09-01 01:30:00.000   0.10
8   2020-09-01 01:45:00.000   0.00
9   2020-09-01 02:00:00.000   0.00



declare @i int = 1, @new_value money = 0.3

while @i <= 10
begin

        select @new_value = iif(value> (@new_value * 0.2) and value < @new_value,
                                       value,
                                       @new_value)
        from table
        where value >= 0.1
        and id = @i + 1;

    set @i = @i + 1
end

select @new_value -- Output will be 0.10

This while loop go through each row and update @new_value based on some conditions and then check with the next row.

I would like to see if there is any way not to use a loop or cursor and get the same result.

Answer

Here it is in recursive cte

declare @tbl table
(
    id  int,
    [value] decimal(5,2)
)

insert into @tbl values
(1, 0.00),
(2, 0.30),
(3, 0.00),
(4, 0.15),
(5, 0.20),
(6, 0.10),
(7, 0.10),
(8, 0.00),
(9, 0.00);

declare @new_value decimal(5,2) = 0.3;

with rcte as
(
    select  id, value, 
            new_value = iif(value > (@new_value * 0.2) 
                        and value < @new_value,
                        value,
                        new_value)
    from    @tbl
    where   id  = 1

    union all

    select  t.id, t.value, 
            new_value = iif(t.value > (r.new_value * 0.2) 
                        and t.value < r.new_value,
                        t.value,
                        r.new_value)
    from    rcte r
            inner join @tbl t   on  r.id    = t.id - 1
)
select  *
from    rcte