question

sqlnewb avatar image
sqlnewb 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
sqldatepoint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

works great!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.