# Calculating average

Discussion in 'SQL Server 2008 General Developer Questions' started by darkangelBDF, May 5, 2010.

1. ### darkangelBDFMember

Good day everyone.
I once again need advice on, what is to me a very irritating change request. We have the current scenario:
We have a value (let's call it DiffValue) that is calculated using the difference between PlanValue - AllocatedValue. This is done per week. I.e. DiffValue = PlanValue - AllocatedValue.
Now the request is to do a calculation (let's call it DiffValue4Weeks) for the total DiffValue of the preceding four weeks. The problem is we have information stored like this:
OTBYear (which is self-explanatory, i.e. 2010 for example).
OTBWeek (which would be the retail week 18 of the year 2010 for example). Weeks are stored per year in order 1 through 52. For a new year it starts again at 1, etc.
The calculation initially seemed simple enough until we realized there could be a problem. What if you are in week 3 of a year? E.g. Year 2010, Week 3. How would you cater for that as the 4 weeks necessary for the calculation would be weeks 1, 2 and 3 of 2010 and then week 52 of 2009.
I hope this makes sense.
2. ### SajidAttarNew Member

Something like this???? If not post sample script to create the a dummy table with some sample data...DECLARE @YearWeek TABLE (ID INT IDENTITY(1,1),OTBYear INT, OTBWeek INT, DIFF INT)
;
WITH WEEKS AS (SELECT 1 AS WK
UNION ALLSELECT WK +1
FROM WEEKS
WHERE WK < 52)INSERT
INTO @YearWeek(OTBYear, OTBWeek, DIFF )SELECT OTBYEAR, WK
,
CONVERT(VARCHAR(2), REVERSE(RAND(OTBYEAR+WK)))FROM
WEEKS WCROSS
JOIN (SELECT 2009 AS OTBYEAR UNION ALL SELECT 2010) AS XWHERE
OTBYEAR = 2009 OR (OTBYEAR =2010 AND WK < 4)ORDER
BY OTBYEAR, WKDECLARE
@OTBYEAR INT =2010, @WEEK INT = 3SELECT
* , (SELECT AVG(B.DIFF) FROM @YearWeek B WHERE B.ID BETWEEN A.ID -3 AND A.ID)FROM
@YearWeek A
WHERE A.OTBYear = @OTBYEAR AND A.OTBWeek = @Week