Refresh table with previous column without any loops

Question

I had this table

test123 rn pds pv bl ag pg 1 6817 90 15 1 1 2 6817 12 1 1 3 6817 10 1 2 4 6817 10 1 3 5 6817 11 1 2 1 6818 92 15 1 1 2 6818 12 1 1 3 6818 10 1 2 4 6818 11 1 3 5 6818 9 1 2 6 6818 8 2 1 

expected output (with output cycles) will be

 test123 rn pds pv bl ag pg 1 6817 90 15 1 1 2 6817 90 12 1 1 3 6817 180 10 1 2 4 6817 540 10 1 3 5 6817 1080 11 1 2 1 6818 92 15 1 1 2 6818 92 12 1 1 3 6818 184 10 1 2 4 6818 552 11 1 3 5 6818 1104 9 1 2 6 6818 2208 8 2 1 

pv can be a product (pv values ​​in the previous line) (ag) (pg) see link

 update b set b.pv=(a.pv*b.ag*b.pg) from test123 a left outer join test123 b on b.pds=a.pds and b.rn=a.rn+1 and a.pds=b.pds; 

Answer 1

If you only mean without a while loop , this can be done with a recursive CTE, like this:

Query:

 with ord as( Select rn, pds, pv, bl, ag, pg, n = ROW_NUMBER() over(partition by pds order by rn) From @data ), loop as ( select pds, n, pv, mx = ag*pg From ord Where n = 1 Union All Select o.pds, on, l.pv, mx * ag*pg From loop l Inner Join ord o on l.pds = o.pds and l.n+1 = on ) Select o.rn, o.pds, pv = l.pv*l.mx, o.bl, o.ag, o.pg From loop l Inner Join ord o on o.pds = l.pds and on = ln Order by o.pds, on; 

ord is used to order pds by rn. loop is a recursive cte. It runs ag * pg with all the previous product.

Your data:

 Declare @data table (rn int, pds int, pv int, bl int, ag int, pg int) Insert into @data(rn, pds, pv, bl, ag, pg) values (1, 6817, 90, 15, 1, 1) , (2, 6817, null, 12, 1, 1) , (3, 6817, null, 10, 1, 2) , (4, 6817, null, 10, 1, 3) , (5, 6817, null, 11, 1, 2) , (1, 6818, 92, 15, 1, 1) , (2, 6818, null, 12, 1, 1) , (3, 6818, null, 10, 1, 2) , (4, 6818, null, 11, 1, 3) , (5, 6818, null, 9, 1, 2) , (6, 6818, null, 8, 2, 1); 

Output:

 rn pds pv bl ag pg 1 6817 90 15 1 1 2 6817 90 12 1 1 3 6817 180 10 1 2 4 6817 540 10 1 3 5 6817 1080 11 1 2 1 6818 92 15 1 1 2 6818 92 12 1 1 3 6818 184 10 1 2 4 6818 552 11 1 3 5 6818 1104 9 1 2 6 6818 2208 8 2 1