SQL-Server Recursive Query

Question

I have some data that resides in a table that I can easily select from and I need to calculate an output value for each row based on the previous row's value, with the exception of the first row, which is calculated using a value I calculate in the select query initially. I have tried multiple iterations of the recursive code and utilizing CTEs but I have been unsuccessful in getting it to work. I am driving myself insane trying to get it working and I would rather not run a loop because it takes to long to complete. We are running sql server 2012 and I am writing the 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   

the code to calculate the first row is

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

the code to calculate the second row through n row is

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

please let me know if there is something I am missing because I have 8760 rows of data that I need to roll through 57 times (each is a different data set) and doing the loop isn't fast enough for what I need it for.

Answer 1

Here is a recursive cte, but I can't speak to the performance of 87,000 rows

;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  #x r
    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'm assuming RN is incremental with no gaps