tricky SQL query

[raydenl]

SELECT NZDate - CAST(NZDate - '2005-01-01' AS int) % 7 AS WeekStarting, sum(score)/7
from myTable
where (NZDate between '2005-01-01' and '2005-01-21') and name IN ('John','Bob','Harry')
group by NZDate - CAST(NZDate - '2005-01-01' AS int) % 7
order by weekstarting

Hi, I have a query (above) that given any start date (2005-01-01) and end date (2005-01-21), returns a result set as such:

2005-01-01 56.4 (contains mean of all values between '2005-01-01' and '2005-01-07')
2005-01-08 64.8
2005-01-15 45.7

This works good, except now I have been asked to change this so it returns the mean for 79 years

i.e. in the example above also gets the values for the three 7 day periods from 1927 to 2005 and averages them

to return a result set that looks like this:

2005-01-01 45.8 (contains mean of all values between '****-01-01' and '****-01-07' for 1927 to 2005)
2005-01-08 56.4
2005-01-15 34.9

Please help me!
Thanks

[raydenl]

Anybody???

I will explain it in a different way.

This query gives me:

select NZDate - CAST(NZDate - '1927-01-01' AS int) % 7 WeekStarting, avg(score) Avg_Score
from myTable
where (NZDate between '1927-01-01' and '2005-01-21') and name IN ('John','Bob','Harry')
group by NZDate - CAST(NZDate - '1927-01-01' AS int) % 7
order by weekstarting

1927-01-01 56.78
1927-01-08 34.56
1927-01-15 56.23
1927-01-22 45.34
1927-01-29 23.23
1927-01-05 45.23
...
2005-01-15 56.23

What I want is this:

1927-01-01 56.78
1927-01-08 34.56
1927-01-15 56.23
1928-01-01 56.78 <-new group
1928-01-08 34.56
1928-01-15 56.23
1929-01-01 56.78 <-new group
1929-01-08 34.56
1929-01-15 56.23
...
2005-01-01 56.78 <-new group
2005-01-08 34.56
2005-01-15 56.23

It is then easy for me to strip of the year and group by the day and month.

The user will give a start and an end date, and in the example above it would have been 2005-01-01 to 2005-01-21 (will always be a multiple of 7 days), and it needs to go back 79 years from the given start year, in this example 1927.

I hope this helps more.