asked
Point back to previous week
Hi I have a interesting problem maybe someone can help with. I want to take the difference between the column "end" of week 2 and the "start" column of week 1.. The first week should have 0 difference because it has no week to compare with. Basically I need to point back to the previous week. Any idea how to do this? I have data setup like this: week start end difference 1 5 10 0 2 4 15 10
If your weeks are consecutive then you can simply join the table back on itself declare @weektable table ( [week] int, start int, [end] int ) insert into @weektable select 1,5,10 insert into @weektable select 2,4,15 insert into @weektable select 3,3,25 insert into @weektable select 4,5,20 select w1.[week], w1.[start], w1.[end], isnull(w1.[end]-w2.start,0) as difference from @weektable w1 left join @weektable w2 on w1.week-1 = w2.week gives week start end difference ----------- ----------- ----------- ----------- 1 5 10 0 2 4 15 10 3 3 25 21 4 5 20 17 but if there are 'gaps' use the row_number() function to determine a consecutive run declare @weektable table ( [week] int, start int, [end] int ) insert into @weektable select 1,5,10 insert into @weektable select 2,4,15 insert into @weektable select 3,3,25 insert into @weektable select 5,5,20 ;with cte as ( select [week], start, [end], row_number() over(order by [week]) as rownum from @weektable ) select w1.[week], w1.[start], w1.[end], isnull(w1.[end]-w2.start,0) as difference from cte w1 left join cte w2 on w1.rownum-1 = w2.rownum gives week start end difference ----------- ----------- ----------- ----------- 1 5 10 0 2 4 15 10 3 3 25 21 5 5 20 17
