SQL Server Recursive Query

Question

I have some data that is in a table that I can easily select, and I need to calculate the output value for each row based on the previous row value, except for the first row, which is calculated using the value. First I calculate the select query. I tried several iterations of recursive code and used CTE, but I was not able to get it to work. I make myself crazy by trying to get it to work, and I would rather not start the loop because it lasts a long time. We are running sql server 2012 and I am writing code in SSMS 2014.

select 1 as rn, 1.5 x1, 2.5 x2, 2.0 x3, 45 y1, 42 y2, 43 ild into #x union all select 2 as rn, 1.7 x1, 2.2 x2, 2.1 x3, 55 y1, 12 y2, 43 ild 

code to calculate the first line

 select x1*y1 + x2*y2 + x3 * ild from #x where rn = 1 

code for calculating the second line through n lines

 select x1*y1 + x2*y2 + x3 * (previous row calculated value) 

please let me know if something is missing to me because I have 8760 rows of data that I need to scroll 57 times (each of them is a different dataset) and the loop is not fast enough for what I need it for.

Answer 1

Here is a recursive cte, but I can not talk about the performance of 87,000 lines

 ;with cte as ( Select rn ,Calc = cast( x1*y1 + x2*y2 + x3 * ild as money) from #x Where rn=1 Union All Select r.rn,cast(r.x1*r.y1 + r.x2*r.y2 + r.x3 * p.Calc as money) From #xr Join cte p on r.rn=p.rn+1 ) Select * from cte Option (MAXRECURSION 0) 

Returns

 rn Calc 1 258.50 2 662.75 

I should note: I assume RN is incremental without spaces