sqlserver back end

Question

how to get (4 week) average in sqlserver next to next four week averahe in sqserver

Rajeesh Menoth · Answer

Hi Try this code : SELECT DATEPART(WData_Date) AS [Week] AVG ([Some_DataField]) AS AverageValue FROM [DataTable] WHERE Data_Date>=DATEADD(W-4GETDATE()) GROUP BY DATEPART(WData_Date) Reference : http://stackoverflow.com/questions/5617520/get-the-last-4-weeks-data-as-averages-in-sqlserver-2008

Vinay Singh · Answer

Hi use avg() in build function to get average SELECT DataYear = N.Serial / 52 DataWeek = N.Serial % 52 + 1 Avg (T.Value) FROM dbo.DataTable T CROSS JOIN ( VALUES (0) (1) (2) (3)) W (Offset) CROSS APPLY ( SELECT T. Year * 52 + Week + W.Offset) N (Serial) GROUP BY N.Serial / 52 N.Serial % 52 + 1 HAVING Count (*) = 4 -- if you don't want smaller sets ORDER BY DataYear DataWeek; For detail check the link http://stackoverflow.com/questions/13965450/calculate-the-average-of-the-rolling-last-4-weeks