SQL Server Performance

Calculating average

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

  1. darkangelBDF Member

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

Share This Page