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