SQL Server – Sum of Previous N Columns Based on Category

aggregatesql servert-sqlwindow functions

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

input

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

enter image description here

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:

enter image description here

Best Answer

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.

Related Question