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...
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With