asked
sum change from weeks
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.
what are the original values of change, or if that what you have given what values of NewChange do you require?
shouldnt the Change value for week 1 be either zero or the value in the Total column for that week? This demonstrates that either the first week is the zero point or that you start from zero and then the week one value affects the change?
This situation is very much like the one you describe in this question:
http://ask.sqlservercentral.com/questions/78575/change-excel-formula-to-sql Can you not adapt that solution to fix this scenario too?
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
If you figured it out - please share _what_ you figured out :)
use a row number instead of the week, or add in extra calculations around the year end
Not without knowing more things: Table definitions Sample data Your current code
Still: You have not revealed your table definitions, your current code, some sample data - all of which is needed to advice further.
