Rolling sum previous 3 months SQL Server

Question

I have the following table structure:

Name           |Type
---------------|-----
fiscal year    | varchar
period         | varchar
country        | varchar
value          | int

I am struggling in SQL Server 2012 with a query that should compute the the sum of previous three months for every distinct month in the table. There might be gaps between months, data is not present for every month and for a given year, month and country could be several rows.

EG:

Year    |Period |Country   |Value
--------|-------|----------|------
2016    |2      |Morovia   |100
2016    |9      |Morovia   |100
2016    |10     |Elbonia   |-20
2016    |10     |Elbonia   |2000
2016    |10     |Elbonia   |200
2016    |10     |Elbonia   |-100
2016    |10     |Elbonia   |1000
2016    |10     |Morovia   |200
2016    |10     |Elbonia   |-200
2016    |10     |Elbonia   |-200
2016    |10     |Elbonia   |100
2016    |10     |Elbonia   |60
2016    |10     |Elbonia   |40
2016    |11     |Morovia   |200
2016    |11     |Elbonia   |100

I am trying to create a result set that looks like:

Year    |Period |Country   |3M Value
--------|-------|----------|--------
2016    |2      |Morovia   |100        - data only for this month
2016    |9      |Morovia   |100        - data only for this month
2016    |10     |Morovia   |300        - current month (200) + previous(100)
2016    |10     |Elbonia   |2880       - data only for this month
2016    |11     |Morovia   |500        - current + previous + 2 month ago
2016    |11     |Elbonia   |2980       - current month(100) + previous(2880)

Answer 1

Assuming you have another table containing Countries

CREATE TABLE Country(Country VARCHAR(50) PRIMARY KEY);
INSERT INTO Country VALUES ('Morovia'),('Elbonia');

then you could create a table with all year/month combinations for the time period of interest

CREATE TABLE YearMonth
(
Year INT,
Month INT,
PRIMARY KEY (Year, Month)
)
INSERT INTO YearMonth
SELECT Year, Month
FROM (VALUES(2015), (2016), (2017)) Y(Year)
CROSS JOIN (VALUES(1), (2), (3),(4), (5), (6),(7), (8), (9), (10), (11), (12)) M(Month)

and then outer join onto that (Demo) as below. For each country it is guaranteed that there will be exactly one row for every month year covered by YearMonth and you can then partition by country and use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to sum them.

WITH Grouped AS
( SELECT  SUM(Value) AS MonthValue,
                  Year,
                  Period,
                  Country
         FROM     Table1
         GROUP BY Year,
                  Period,
                  Country ), Summed AS
( SELECT    YM.*,
                      C.Country,
                      G.Year AS GYear,
                      [3M Value] = SUM(MonthValue) OVER (partition BY C.Country 
                                                   ORDER BY YM.Year, YM.Month 
                                                   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
           FROM       YearMonth YM
           CROSS JOIN Country C
           LEFT JOIN  Grouped G
           ON         G.Year = YM.Year
                      AND G.Period = YM.Month
                      AND G.Country = C.Country )
SELECT   *
FROM     Summed
WHERE    GYear IS NOT NULL
ORDER BY Year,
         Month,
         Country

---

Or a version with the same semantics but potentially more efficient

SELECT      CA.*
FROM        Country C
CROSS APPLY
(
SELECT    YM.*,
          C.Country,
          G.Year AS GYear,
          [3M Value] = SUM(MonthValue) OVER ( ORDER BY YM.Year, YM.Month 
                                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM      YearMonth YM
LEFT JOIN 
(
SELECT SUM(Value) AS MonthValue,
       Year,
       Period
FROM   Table1 T
WHERE T.Country = C.Country
GROUP  BY Year,
          Period) G
ON        G.Year = YM.Year
          AND G.Period = YM.Month
                     ) CA
 WHERE   GYear IS NOT NULL
ORDER BY    Year,
            Month

Answer 2

Another approach using Outer JOIN

;WITH cte
     AS (SELECT year,
                period,
                country,
                Sum(value) AS sumvalue
         FROM   Yourtable 
         GROUP  BY year,
                   period,
                   country)
SELECT a.Year,
       a.Period,
       a.Country,
       a.sumvalue + Isnull(Sum(b.sumvalue), 0) as [3M Value]
FROM   cte a
       LEFT JOIN cte b
              ON a.Country = b.Country
                 AND Datefromparts(b.[Year], b.Period, 1) IN ( Dateadd(mm, -1, Datefromparts(a.[Year], a.Period, 1)), Dateadd(mm, -2, Datefromparts(a.[Year], a.Period, 1)) )
GROUP  BY a.Year,
          a.Period,
          a.Country,
          a.sumvalue 

• Live Demo

Answer 3

If the months are not available, then you can use a non-equijoin or outer apply:

with t as (
      select year, period, country, sum(value) as sumvalue
      from t
      group by year, period, country
     )
select t.*, tt.sumvalue_3month
from t outer apply
     (select sum(t2.sumvalue) as sumvalue_3month
      from t t2
      where t.country = t2.country and
            t2.year * 12 + t2.period >= t.year * 12 + t.period - 2 and
            t2.year * 12 + t2.period <= t.year * 12 + t.period
     ) tt;

The CTE summarizes the data by year, period, and month. The outer apply then sums for the previous three months. The trick is to convert the year and period values to months since time zero.

Actually, a simpler method is to do the yyyymm calculation in the CTE:

with t as (
      select year, period, country, sum(value) as sumvalue,
             (t.year * 12 + t.period) as month_count
      from t
      group by year, period, country
     )
select t.*, tt.sumvalue_3month
from t outer apply
     (select sum(t2.sumvalue) as sumvalue_3month
      from t t2
      where t.country = t2.country and
            t2.month_count >= t.month_count - 2 and
            t2.month_count <= t.month_count
     ) tt;