[SOLVED] SQL Server : get average per week for the past 30 weeks

SQL Server : get average per week for the past 30 weeks

I'm trying to figure out how to get the average CHECK_AMOUNT per week for the past/last 30 weeks in SQL Server 2008.

I tried something like this (see below) but I I think that is for months and not for weeks.

SELECT TOP 30
    AVG(CHECK_AMOUNT) AS W2 
FROM 
    CHECKS 
WHERE 
    NOT UNT='256' 
GROUP BY 
    YEAR(DATE_GIVEN), MONTH(DATE_GIVEN)

Can anyone show me how I can make that possible please,

Thank you...

Solution

Just use a where clause comparing the dates:

  select AVG(CHECK_AMOUNT)
  from CHECKS
  WHERE NOT UNT='256' and DATEDIFF(d, Date_Given, getdate()) <= 30*7

I'm sorry; I misread the question. You want the average per week. Your original query is quite close

  select DATEDIFF(d, Date_Given, getdate())/7 as weeks_ago, AVG(CHECK_AMOUNT)
  from CHECKS
  WHERE NOT UNT='256' and DATEDIFF(d, Date_Given, getdate()) <= 30*7
  group by DATEDIFF(d, Date_Given, getdate())/7

I'm leaving the where clause in for selecting -- rather than using top 30 -- in case there are weeks with no checks.