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

 

[amrita418]

This should get you started:

select 		'2005-' + A.WeekStarting WeekStart, A.Avg_Score
from 		select 	  	substring(convert(varchar(10), (NZDate - CAST(NZDate - '1927-01-01' AS int) % 7)), 6, 5) 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  	substring(convert(varchar(10), (NZDate - CAST(NZDate - '1927-01-01' AS int) % 7)), 6, 5)) A
order by	WeekStart

PS: Code Not Tested.

Regards,
AA

 

[raydenl]

That is not quite what I am after. 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.

 

[pwilson]

I dont know if this will help but it should only pull out dates that are in the first 21 days of Jan.

Try this in the WHERE clause

DATEDIFF(dd, DATEADD(yy,DATEDIFF(yy,0,DateColumn)),'1/1/1900'), DateColumn) < 21

select GetDate(),
OutVar = CASE 
WHEN DATEDIFF(dd, DATEADD(yy,DATEDIFF(yy,0,'1/22/05')),'1/1/1900'), '1/22/05') < 21 THEN 1
ELSE 0 END

'1/22' should return false
'1/21' should return true