-- 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

Create Table Ex1 
	(
		YEAR int, 
		WEEKS int, 
		VALUE int 
	)
	
Insert Into Ex1 
Select 2012,	 1,		3000
Union ALL 
Select 2012,	 2,		5000
Union ALL 
Select 2012,	 3,		6000
Union ALL 
Select 2012,	 4,		7000
Union ALL 
Select 2012,	 5,		8000
Union ALL 
Select 2012,	 6,		9000
Union ALL 
Select 2012,	 7,		1000
Union ALL 
Select 2012,	 8,		6000 

;With CTE 
As
	(
		Select *, ROW_NUMBER() Over(Order By Year, Weeks) As rn From Ex1 
	)
Select *, (Case When (Select Count(*) From CTE Where rn < a.rn) >= 4 Then (Select AVG(VALUE) From CTE As b Where b.rn IN(Select Top 4 rn From CTE Where rn < a.rn Order By rn DESC)) Else '' End) As AvgValue
From CTE As a
Order By YEAR, WEEKS 

SELECT year, week, value, ISNULL(AvgVal,0) AS AvgVal
FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
      FROM year_week_value) a(year,week,value,pos)
OUTER APPLY (SELECT SUM(b.value)/(COUNT(1)*1.00)
             FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
                   FROM year_week_value) b(year,week,value,pos)
             WHERE a.pos >= b.pos AND a.pos-4 <= b.pos) ab(AvgVal);

SELECT year, week, value, ISNULL(AvgVal,0) AS AvgVal
FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
      FROM year_week_value) a(year,week,value,pos)
OUTER APPLY (SELECT SUM(b.value)/(COUNT(1)*1.00)
             FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
                   FROM year_week_value) b(year,week,value,pos)
             WHERE a.pos > b.pos AND a.pos-5 < b.pos) ab(AvgVal);

-- 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);

-- Lokesh Vij
SELECT a.year, a.week, .25 * (Isnull(b.value, 0) + Isnull(c.value, 0) + Isnull(d.value, 0) + Isnull(e.value, 0)) AvgVal, A.value
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;

-- vinu512
WITH CTE
AS (SELECT *, ROW_NUMBER() OVER (ORDER BY year, week) AS rn
    FROM year_week_value
    )
SELECT *, (CASE WHEN (SELECT Count(*)
                      FROM CTE
                      WHERE rn < a.rn) >= 4 
                THEN (SELECT AVG(VALUE)
                      FROM CTE AS b
                      WHERE b.rn IN (SELECT TOP 4 rn
                                     FROM CTE
                                     WHERE rn < a.rn
                                     ORDER BY rn DESC
                                     )) 
                ELSE '' 
           END) AS AvgValue
FROM CTE AS a
ORDER BY year, week;
 
-- Cadavre
SELECT year, week, value, ISNULL(AvgVal,0) AS AvgVal
FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
      FROM year_week_value) a(year,week,value,pos)
OUTER APPLY (SELECT SUM(b.value)/(COUNT(1)*1.00)
             FROM (SELECT year, week, value, ROW_NUMBER() OVER(ORDER BY year,week)
                   FROM year_week_value) b(year,week,value,pos)
             WHERE a.pos >= b.pos AND a.pos-4 <= b.pos) ab(AvgVal);

---------------------------------------------------------------------------
Lokesh Vij
---------------------------------------------------------------------------
year        week        AvgVal                                  value
----------- ----------- --------------------------------------- -----------
2011        49          0.00                                    3000
2011        50          750.00                                  5000
2011        51          2000.00                                 6000
2011        52          3500.00                                 7000
2012        1           5250.00                                 3000
2012        2           5250.00                                 5000
2012        3           5250.00                                 6000
2012        4           5250.00                                 7000
2012        5           5250.00                                 8000
2012        6           6500.00                                 9000
2012        7           7500.00                                 1000
2012        8           6250.00                                 6000
---------------------------------------------------------------------------
---------------------------------------------------------------------------
vinu512
---------------------------------------------------------------------------
year        week        value       rn                   AvgValue
----------- ----------- ----------- -------------------- -----------
2011        49          3000        1                    0
2011        50          5000        2                    0
2011        51          6000        3                    0
2011        52          7000        4                    0
2012        1           3000        5                    5250
2012        2           5000        6                    5250
2012        3           6000        7                    5250
2012        4           7000        8                    5250
2012        5           8000        9                    5250
2012        6           9000        10                   6500
2012        7           1000        11                   7500
2012        8           6000        12                   6250
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Cadavre
---------------------------------------------------------------------------
year        week        value       AvgVal
----------- ----------- ----------- ---------------------------------------
2011        49          3000        0.000000000000000
2011        50          5000        3000.000000000000000
2011        51          6000        4000.000000000000000
2011        52          7000        4666.666666666666666
2012        1           3000        5250.000000000000000
2012        2           5000        5250.000000000000000
2012        3           6000        5250.000000000000000
2012        4           7000        5250.000000000000000
2012        5           8000        5250.000000000000000
2012        6           9000        6500.000000000000000
2012        7           1000        7500.000000000000000
2012        8           6000        6250.000000000000000