SQL Question

Database

SQL Question

Here is the problem:

STMT_NUM OS SLOPE
1 1000 1
2 3000 5
3 4400 3
4 9000 2
5 1500 7

I want a derived field that is like the following (column Prime)

STMT_NUM OS SLOPE PRIME
1 1000 1 1000 (Same as OS for stmt_num =1)
2 3000 5 1000 + 5*(1000) (=6000)
3 4400 3 6000 + 3*(6000) (=24000)
4 9000 2 24000 + 2*(24000) (=72000)
5 1500 7 72000 + 7*(72000) ……

Basically what I want is a column that is: (previous value of the SAME column) + Slope*(previous value of the SAME column)
1 REPLY
N/A

Re: SQL Question

Hi chanduks,
it's easy to solve using recursion.
There's probably another solutiuon doing some tricks with aggregates, but i can't remember where to find it, so i'll do the simple one:

with recursive cte (stmt_num, os, slope, prime) as
(select
stmt_num, os, slope,
os as prime
from chanduks
where stmt_num = 1

union all
select
c.stmt_num, c.os, c.slope,
cte.prime * (c.slope + 1)
from chanduks as c
join cte on c.stmt_num = cte.stmt_num + 1
)
select * from cte
order by 1

Dieter