Zee,
Couple of questions... trying to help a bit, if needed...
First, how long does it take your final code (thank you for posting it) to run on all of those rows? In the absense of any other info, it looks to me like it's gonna do 4 full table scans.
Second, you never said what the first day of a week was (or, at least I missed that part)...
Third, why do you think there's only 7 days between 11/04 and 11/11... there's actually 8 because you must count 11/04 as the first day...
11/04 = 1
11/05 = 2
11/06 = 3
11/07 = 4
11/08 = 5
11/09 = 6
11/10 = 7
11/11 = 8
--Jeff Moden
Change is inevitable... Change for the better is not.