I have table in which I have to update the number of users on that particular date and number of users in past 7 days from the given date. Sample data looks like
Date UserCountToday UserCount7days
20120911 907575
20120910 953629
20120909 1366180
20120908 1388916
20120907 1009425
20120906 918638
20120905 956770
20120904 1018152
20120903 1306341
try this:
with cte as
(select *,ROW_NUMBER() over(order by [Date]) as row_num
from t_users)
select [Date],UserCountToday, (select SUM(UserCountToday)
from cte c1 where c.row_num>=c1.row_num
and abs(c.row_num-c1.row_num)<7) as UserCount7days
from cte c
result
Date UserCountToday UserCount7days
20120903 1306341 1306341
20120904 1018152 2324493
20120905 956770 3281263
20120906 918638 4199901
20120907 1009425 5209326
20120908 1388916 6598242
20120909 1366180 7964422
20120910 953629 7611710
20120911 907575 7501133