SQL Roll Up Last 4 Week by week

Question

I have a raw data as shown in table below. I want to get(Sum up) previous 4 weeks data.Can any one guide me how to get this by SQL Select query.

Raw data table :

Week    Year    Category    Weekly Total
9       2017      Motor           8
8       2017      Car             7
8       2017      Motor           5
7       2017      Car             4
6       2017      Car             8
5       2017      Car             16
5       2017      Motor           15
4       2017      Car             8
3       2017      Car             5
2       2017      Car             3
1       2017      Car             12
52      2016      Car             8
51     2016       Car             6

Expected Output:

Week    Year    4Weeks Total
9       2017        32
8       2017        55
7       2017        51
6       2017        52
5       2017        47
4       2017        28
3       2017        28
2       2017        29
1       2017        26
52      2016        14
51      2016        6

What version of SQL are you using? – Tim Biegeleisen Feb 26 '17 at 12:42 — Tim Biegeleisen, Feb 26 '17 at 12:42

score 0 · Accepted Answer · answered Feb 26 '17 at 12:52

You can use apply:

with t as (
      select week, year, sum(weeklytotal) as weeklytotal
      from t
      group by week, year
     )
select t.week, t.year, t4.total4
from t outer apply
     (select sum(t4.weeklytotal) as total4
      from (select t4.*
            from t t4
            where t4.year < t.year or
                  (t4.year = t.year and t4.week <= t.week)
            order by t4.year desc, t4.week desc
           ) t4
     ) t4;

This assumes (as in your sample data) that you have data for every week.

Gurwinder Singh · Answer 2 · 2017-02-26T13:19:45.303

You can find the aggregate in a CTE. After that you can find the required 4 week sum in a correlated subquery.

with cte
as (
    select week, year, sum(weekly_total) total
    from your_table
    group by week, year
    )
select week, year, (
        select sum(total)
        from (
            select top 4 total
            from cte t2
            where t2.year * 100 + t2.week <= t1.year * 100 + t1.week
            order by year desc, week desc
            ) x
        )
from cte t1;

Produces:

week    year    total
9       2017    32
8       2017    55
7       2017    51
6       2017    52
5       2017    47
4       2017    28
3       2017    28
2       2017    29
1       2017    26
52      2016    14
51      2016    6

SQL Roll Up Last 4 Week by week

2 Answers2

Demo