How do I get 7 day rolling average based on the last 12 months?

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.

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

Seems like you could use a window function here.

You just need to make sure that your data has a single row per day. You can do this by grouping first.

SELECT
  *,
  SUM(DailyAmount) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WeeklyAverageAmount
FROM (
    SELECT
      t.Date,
      SUM(t.Amount) AS DailyAmount
    FROM YourTable t
    GROUP BY
      t.Date
) ByDay;

Just note that summing or adding amounts produces a TOTAL and not an AVERAGE.

Your sample data has multiple rows for only the first day of a MONTH, so I do not see how you can ever get a 7 day average from that. (Unless you're using yyyy-dd-MM format, but I've never seen anybody do that.)

You should typically not be relying on the number of rows if you're looking for a specific date range - rather look at the dates themselves.

So, I am not really sure what you are looking for exactly, but maybe this can help you get on track:

-- prep data
select * 
into #tmp
from ( values 
    ('2020-01-01',  3500.03),
    ('2020-01-01',  3000.03),
    ('2020-01-01',  3200.86),
    ('2020-01-01',  4500.00),
    ('2020-02-01',  2100.23),
    ('2020-02-01',  1000.00),
    ('2020-03-01',  2800.93)
)t(date, amount)



-- output 
select 
    t.Date
    ,t.amount
    ,sum(amount) over (order by date) as running_total
    ,avg(amount) over (order by date) as running_avg
    ,tca.rolling_sevenday_total
    ,tca.rolling_sevenday_avg
from #tmp t
cross apply (
    select 
        sum(amount) as rolling_sevenday_total,
        avg(amount) as rolling_sevenday_avg
    from #tmp t7
    where 
        t7.date between dateadd(dd, -7, t.date) and t.date

) tca

And here's what it will look like if your data if your data is actually per day

-- prep data
select * 
into #tmp2
from ( values 
    ('2020-01-01',  3500.03),
    ('2020-01-02',  3000.03),
    ('2020-01-03',  3200.86),
    ('2020-01-04',  4500.00),
    ('2020-01-05',  4500.00),
    ('2020-01-06',  4500.00),
    ('2020-01-07',  4500.00),
    ('2020-02-01',  2100.23),
    ('2020-02-02',  1000.00),
    ('2020-02-03',  2800.93)
)t(date, amount)



-- output 
select 
    t.Date
    ,t.amount
    ,sum(amount) over (order by date) as running_total
    ,avg(amount) over (order by date) as running_avg
    ,tca.rolling_sevenday_total
    ,tca.rolling_sevenday_avg
from #tmp2 t
cross apply (
    select 
        sum(amount) as rolling_sevenday_total,
        avg(amount) as rolling_sevenday_avg
    from #tmp2 t7
    where 
        t7.date between dateadd(dd, -7, t.date) and t.date

) tca