Search code examples
sqlsql-serverwindow-functionscalculated-columnscalculation

Moving calculations using SQL

I need to calculate a new column using moving calculations.

For example, I have a table:

A B
10 15
11 14
12 13

I need to calculate new column where the 1st value is calculated like 5000/10*15, the 2nd value is (5000 / 10 * 15) / 11 * 14, the 3rd one is ((5000 / 10 * 15) / 11 * 14) / 12 * 13 and so on. Where 5000 is a random value and in the future I will use it like a parameter in a stored procedure.

I know, that in Excel for example we can reffer to the previous calculated cell. How can it be calculated using SQL?

Thank you!

Solution 
  • create table #test (A int,B int)

insert into #test values(10,15),(11,14),(12,13)

declare @seed int=5000;

;with temp as (
 select A,B,row_number() over(order by a) rn from #test
),
cte as
(
 select @seed/A*B  calculated,rn from temp where rn=1
 union all
 select c. calculated/t.A*t.B,t.rn from temp t
 join cte c on t.rn=c.rn+1
)
select * from cte