I am editing this question to make it clearer. What I have is basically 3 columns. The first column is the WEEK the second TOTAL and the third is CHANGE. I want to create a new column that I will call NewChange it will take the previous weeks change and add it to the curreent weeks Total. The change column was added by me as an empty column exept I set it so the min week will be equal to 1 so it always starts at 1 for the first calculation. Currently my query is calulating the first week correctly but then after it is not carrying the new value over to calculate It works for the first two weeks but then it starts adding the previous and current rows from the Total column only. select a.week, a.total, a.Change, (a.Total + b.Change) as NewChange from sample a left join sample b on a.week-1 = b.week This is what I want: week Total change NewChange 1 3 1 Null 2 5 0 4 3 12 0 8 4 7 0 -1 etc... I have done this query for previous columns in this data set but for some reason this column the query I have used previously isn't working. Maybe because of the empty column I added. it is adding the 0 instead of the new value.
Like @Fatherjack says - this is very, very similar to you other question. But I know recursion can be a tricky concept, and the recursive CTE syntax helps fuzzing it up, so here's code to this question as well. The trick is to create a CTE with a base step (week=1) and a recursive step which is in some way related to the CTE itself. declare @t table (week int, total int) insert into @t (week, total) select 1, 2 union all select 2,5 union all select 3,12 union all select 4,7 union all select 5,14 union all select 6,18 ;with cte as( select week, total, 1 as change from @t where week=1 union all select t.week, t.total, t.total - cte.change from @t t inner join cte on cte.week = t.week-1) select * from cte