1

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

  • 1
    Which version of SQL Server are you using? In 2012, LAG will probably be your best option. Prior to that, you're stuck with some rather messy business. – Erik Darling Jul 25 '17 at 23:18
  • 1
    version 11.0.2100.60 (SQL Server Express 2012). Trying the LAG stuff and see how far I can get to. – user3885927 Jul 25 '17 at 23:20
2

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.

  • Thank you @joanolo ! Can this by dynamic for different values of X with the OVER clause? – user3885927 Jul 25 '17 at 23:48
  • I am not sure what you exactly mean... I know that the 2 must be a constant value, it won't work with an expression such as (3-1). I guess you can use, in any case sp_executesql – joanolo Jul 25 '17 at 23:54
  • 1
    Yes I was looking for expression such as ' ..ROWS @X Preceding ...'. Doesn't work with OVER but it seems I can use 'select TOP (@x)' in your second sql using subquery. +1 for this help. – user3885927 Jul 26 '17 at 0:01
  • 1
    Glad to help. If you think this is the answer you were looking for, mark it as accepted, so that people can concentrate on the unanswered questions. – joanolo Jul 26 '17 at 0:03
  • Sure, if you can edit the answer to use 'select TOP (@x)' that would make it complete for an answer. – user3885927 Jul 26 '17 at 0:07
1

Here is what I got with the LAG hint from @sp_BlitzErik and the result is what I exactly wanted.

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

But this is not generic enough, say for X number of weeks. Please post if you have better answers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.