CODEWITHSUNDEEP
sql-serversql-server-2008
user4914660
user4914660

Reputation:

update the table with the previous column with out any loops

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 out loops) 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 the product of (pv value in the previous row)(ag)(pg) please look into this 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;

Upvotes: 4

Views: 75

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

If you only mean without a while loop, it 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, o.n, l.pv, mx * ag*pg From loop l
    Inner Join ord o on l.pds = o.pds and l.n+1 = o.n
)
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 o.n = l.n
Order by o.pds, o.n;

ord is used to order you pds by rn. loop is the recursive cte. It does the ag*pg with all 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

Upvotes: 2

Related Questions