I have example of the data below:

The data goes on like this covering a couple of years to present. My goal is to find the 7 day rolling average based on the previous 12 months. I have been trying to use a window function however I got wildly wrong results and I'm out of ideas despite research. If someone could help me out I'd appreciate it.

Edit, the exact code I tried is on another machine I currently do not have access to but I tried like the below:

SELECT 
DATE
,AMOUNT
,SUM(AMOUNT) OVER(PARTITION BY DATE ORDER BY DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WEEKAVG
FROM TABLE

CodePudding user response：

There are many ways to skin this cat—especially if you care about maximum performance—but one approach is something like this:

WITH 
    DailyAmounts AS 
        (
        SELECT
            Date,
            SUM(Amount) AS DailyAmount
        FROM
            Table
        GROUP BY
            Date
        ) DailyAmounts

SELECT
    *,
    (
    SELECT 
        SUM(DailyAmount)
    FROM
        DailyAmounts DailyAmounts2
    WHERE
        Date BETWEEN 
            DATEADD(DAY, -6, DailyAmounts.Date)
            AND DailyAmounts.Date
    ) AS WeeklyAverageAmount
FROM
    DailyAmounts
;

In essence, create a daily amounts table via CTE; then, add a weekly average column and populate via subquery on the same CTE.

CodePudding user response：

If I understand correctly. You need weekly avarage. You can use like this :

set datefirst 1;
WITH tempTable AS (SELECT datepart(week, yourDateColumn) as weekNr, amount from testTable)

Select weekNr, SUM(amount) from tempTable
group by weekNr

If you need daily avarage data query can be like that :

WITH tempTable AS (SELECT DATENAME(WEEKDAY, yourDateColumn) AS weekDay, amount from testTable)
Select weekDay, SUM(amount) from tempTable
group by weekDay