I apologize in advance if there is a similar post already solved up here but I have been searching for days and can't find what I am looking for.
I am working on a database that is gathering information for people who are on a study. Each individual on the study is supposed to make 4 calls a day so there should be 28 calls for a week. If the person makes all 28 calls for a week they get a bonus. They can get a bonus every week they make 28 calls. datepart("ww",date()) is very close to what I want but this numbers the weeks by calendar year. Each individual can have a different start date. I would like the query to group by weeks but the week starting with the individual's start date. So if the person starts today I would like it to look something like this:
Week Week Range Data
Week 1 2/15/12 - 2/21/12 20
Week 2 2/22/12 - 2/28/12 28
I really don't need to see the date range but I just put that there so you can see what I am looking for. Is this possible? Someone please help before I pull all the hair out of my head!!