Here you go..

-- Creating table

CREATE TABLE year_week_value

(

year INT,

week INT,

value INT

);

-- Inserting test values

INSERT year_week_value

VALUES

(2011,49,3000),

(2011,50,5000),

(2011,51,6000),

(2011,52,7000),

(2012,1,3000),

(2012,2,5000),

(2012,3,6000),

(2012,4,7000),

(2012,5,8000),

(2012,6,9000),

(2012,7,1000),

(2012,8,6000);

-- Resultant query

SELECT a.year,

a.week,

.25 * ( Isnull(b.value, 0) + Isnull(c.value, 0)

+ Isnull(d.value, 0) + Isnull(e.value, 0) ) AvgVal

FROM year_week_value a

LEFT JOIN year_week_value b

ON b.year = Year(Dateadd(wk, a.week - 2, Dateadd(year, a.year - 1900, 0)))

AND b.week = Datepart(wk, Dateadd(wk, a.week - 2, 0))

LEFT JOIN year_week_value c

ON c.year = Year(Dateadd(wk, a.week - 3, Dateadd(year, a.year - 1900, 0)))

AND c.week = Datepart(wk, Dateadd(wk, a.week - 3, 0))

LEFT JOIN year_week_value d

ON d.year = Year(Dateadd(wk, a.week - 4, Dateadd(year, a.year - 1900, 0)))

AND d.week = Datepart(wk, Dateadd(wk, a.week - 4, 0))

LEFT JOIN year_week_value e

ON e.year = Year(Dateadd(wk, a.week - 5, Dateadd(year, a.year - 1900, 0)))

AND e.week = Datepart(wk, Dateadd(wk, a.week - 5, 0))

ORDER BY a.year,

a.week