Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category.

If the x is 3 the output would look like this:

Note that the last value is 49 because it added only last two week's values to the current week since x=3.

I am looking to write the SQL as a stored procedure and need some help on appropriate ways of doing this.

With the help from @sp_BlitzErik I tried to use LAG but couldn't quite get where I need to. Here is my query:

SELECT category
    ,year
    ,week
    ,value
    ,(
        LAG(value, 1, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + LAG(value, 2, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + value
        ) AS cumulative_value
FROM valuedata

And the output is not quite correct yet:

On SQL Server 2014 and 2016 1 you can use a WINDOW function (i.e. an OVER clause) to perform what you want:

SELECT
     category, year, week, value, 
     sum(value) OVER (PARTITION BY category 
                          ORDER BY year, week 
                              ROWS 2 PRECEDING) AS retention_value_3_weeks
FROM
     t 
ORDER BY
    category, year, week ;

And this is the result you'll get:

category | year | week | value | retention_value_3_weeks
:------- | ---: | ---: | ----: | ----------------------:
a | 2016 | 1 | 5 | 5
a | 2016 | 2 | 7 | 12
a | 2016 | 3 | 8 | 20
b | 2016 | 3 | 6 | 6
b | 2016 | 4 | 15 | 21
b | 2016 | 5 | 25 | 46
c | 2016 | 3 | 25 | 25
c | 2016 | 4 | 2 | 27
c | 2016 | 5 | 21 | 48
c | 2016 | 6 | 26 | 49

NOTE that the x = 3 of your example gets translated to (the current row and the 2 preceding ones).

If, for some reason, you can't use the OVER clause, you can still compute the same results using some (quite convoluted) subqueries:

SELECT
    category, year, week, value,
    (SELECT 
        sum(value)
     FROM
        (SELECT TOP 3                    /* total number of rows to consider */
             value
        FROM 
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t 
ORDER BY
    category, year, week ;

Check it all at dbfiddle here

If you want to use @x instead of 3, you can do so:

DECLARE @x AS INTEGER = 3;

SELECT
    category, year, week, value,
    (SELECT 
        sum(value)
     FROM
        (SELECT TOP (@x)                  /* total number of rows to consider */
             value
        FROM 
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t 
ORDER BY
    category, year, week ;;

dbfiddle here

1) Can't test with SQL Server 2012 because I don't have one. The documentation from MS SQL Server indicate it should be available since version 2008.

StackExchange Database Administrators Q#181773, answer score: 5