Creating a loop in SQL with reference to previous iteration

Question

I have a dataset with columns:

What I need to do is to create a loop with such conditions:

1. For Index = 1

• if Column1 >= 1 then Column2 = 1 and Column3 = Column1 - 1
• if Column1 < 1 then Column 2 = 0 and Column3 = 0

2. For Index > 1

• if Column1 + Column3 (from previous iteration!) >=1 then Column2 = 1 and Column3 = Column1 + Column3 (from previos iteration!) - 1
• if Column1 + Column3 (from previous iteration) < 1 then Column2 = 0 and Column3 = 0

I need to update this column one row at the time, starting with Index = 1. I have tried to start with such WHILE loop:

WHILE (SELECT COUNT(*) FROM #TABLE WHERE Column2 IS NULL) > 0
UPDATE TOP (1) #TABLE
SET ... 

And I simply can't achieve my desired outcome like this:

And of course I really, really want to avoid using Cursor. Thank you for your help!

Answer 1

You can use a recursive CTE for this, something like:

with t as
(
  select * 
  from (values (1,4,null,null),
               (2,0,null,null),
               (3,0,null,null),
               (4,0,null,null),
               (5,2,null,null),
               (6,0,null,null)) v(Id,Column1,Column2,Column3)
), q as
(
  select Id,
         Column1,
         case when Column1 >=1 then 1 else 0 end Column2,
         case when Column1 >=1 then Column1-1 else 0 end Column3
  from t 
  where id = 1
  union all
  select t.id, 
         t.Column1, 
         case when t.Column1 + q.Column3 >= 1 then 1 else 0 end Column2,
         case when t.Column1 + q.Column3 >= 1 then t.Column1+q.Column3-1 else 0 end Column3
         
  from t
  join q
    on q.id = t.id-1
)
select *
from q

outputs

Id          Column1     Column2     Column3
----------- ----------- ----------- -----------
1           4           1           3
2           0           1           2
3           0           1           1
4           0           1           0
5           2           1           1
6           0           1           0

(6 rows affected)