sql server rolling 12 months sum with date gaps

Question

Suppose I have a table that indicates the number of items sold in a particular month for each sales rep. However, there will not be a row for a particular person in months where there were no sales. Example

rep_id     month_yr     num_sales    
1          01/01/2012    3    
1          05/01/2012    1    
1          11/01/2012    1    
2          02/01/2012    2    
2          05/01/2012    1  

I want to be able to create a query that shows for each rep_id and all possible months (01/01/2012, 02/01/2012, etc. through current) a rolling 12 month sales sum, like this:

rep_id     month_yr     R12_Sum    
1          11/01/2012   5    
1          12/01/2012   5    
1          01/01/2013   5    
1          02/01/2013   2

I have found some examples online, but the problem I'm running into is I'm missing some dates for each rep_id. Do I need to cross join or something?

Answer 1

It is pretty simple using a join with 2 conditions. Consider 2 years of data (2022-2023) for every rep with no sales in some months.

Aggregate at rep, month level in the following manner.

Let's consider May 2023. Apply 2 conditions:

1. Join only previous or current months, not future (Nothing after May 2023 which leaves you with Jan 2022 to May 2023)

2. Take only those months that come after Last Year's May (which leaves you with June 2022 to May 2023)

In this manner, even if Sept 2022 is missing, it won't matter to you. Hope this helps!

SELECT 
  a.rep_id,
  a.month_yr,
  SUM(b.R12_Sum) AS R12_TTM

FROM YourTable a
LEFT JOIN YourTable b 
     ON a.rep_id = b.rep_id
        AND a.month_yr >= b.month_yr
        AND DATEADD(MONTH, -11, a.month_yr) <= b.month_yr

GROUP BY a.rep_id, a.month_yr

Answer 2

The following demonstrates using a CTE to generate a table of dates and generating a summary report using the CTE. Sales representatives are omitted from the results when they have had no applicable sales.

Try jiggling the reporting parameters, e.g. setting @RollingMonths to 1, for more entertainment.

-- Sample data.
declare @Sales as Table ( rep_id Int, month_yr Date, num_sales Int );
insert into @Sales ( rep_id, month_yr, num_sales ) values
  ( 1, '01/01/2012', 3 ),
  ( 1, '05/01/2012', 1 ),
  ( 1, '11/01/2012', 1 ),
  ( 2, '02/01/2012', 1 ),
  ( 2, '05/01/2012', 2 );
select * from @Sales;

-- Reporting parameters.
declare @ReportEnd as Date = DateAdd( day, 1 - Day( GetDate() ), GetDate() ); -- The first of the current month.
declare @ReportMonths as Int = 6; -- Number of months to report.
declare @RollingMonths as Int = 12; -- Number of months in rolling sums.

-- Report.
--   A CTE generates a table of month/year combinations covering the desired reporting time period.
with ReportingIntervals as (
  select DateAdd( month, 1 - @ReportMonths, @ReportEnd ) as ReportingInterval,
    DateAdd( month, 1 - @RollingMonths, DateAdd( month, 1 - @ReportMonths, @ReportEnd ) ) as FirstRollingMonth
  union all
  select DateAdd( month, 1, ReportingInterval ), DateAdd( month, 1, FirstRollingMonth )
    from ReportingIntervals
    where ReportingInterval < @ReportEnd )
  -- Join the CTE with the sample data and summarize.
  select RI.ReportingInterval, S.rep_id, Sum( S.num_sales ) as R12_Sum
    from ReportingIntervals as RI left outer join
      @Sales as S on RI.FirstRollingMonth <= S.month_yr and S.month_yr <= RI.ReportingInterval
    group by RI.ReportingInterval, S.rep_id
    order by RI.ReportingInterval, S.rep_id

Answer 3

It's certainly not pretty but is more simple than a CTE, numbers table or self join:

DECLARE @startdt DATETIME

SET @startdt = '2012-01-01'

SELECT rep_id, YEAR(month_yr), MONTH(month_yr), SUM(num_sales)
FROM MyTable WHERE month_yr >= @startdt AND month_yr < DATEADD(MONTH,1,@startdt)

UNION ALL

SELECT rep_id, YEAR(month_yr), MONTH(month_yr), SUM(num_sales)
FROM MyTable WHERE month_yr >= DATEADD(MONTH,1,@startdt) AND month_yr < DATEADD(MONTH,2,@startdt)

UNION ALL

SELECT rep_id, YEAR(month_yr), MONTH(month_yr), SUM(num_sales)
FROM MyTable WHERE month_yr >= DATEADD(MONTH,2,@startdt) AND month_yr < DATEADD(MONTH,3,@startdt)

UNION ALL

SELECT rep_id, YEAR(month_yr), MONTH(month_yr), SUM(num_sales)
FROM MyTable WHERE month_yr >= DATEADD(MONTH,3,@startdt) AND month_yr < DATEADD(MONTH,4,@startdt)

UNION ALL

etc etc

Answer 4

To solve this problem, you need a driver table that has all year/month combinations. Then, you need to create this for each rep.

The solution is then to left join the actual data to this driver and aggregate the period that you want. Here is the query:

with months as (
    select 1 as mon union all select 2 union all select 3 union all select 4 union all
    select 5 as mon union all select 6 union all select 7 union all select 8 union all
    select 9 as mon union all select 10 union all select 11 union all select 12
   ),
    years as (select 2010 as yr union all select 2011 union all select 2012 union all select 2013
   ),
    monthyears as (
     select yr, mon, yr*12+mon as yrmon
     from months cross join years
    ),
     rmy as (
     select *
     from monthyears my cross join
          (select distinct rep_id from t
          ) r
    )
select rmy.rep_id, rmy.yr, rmy.mon, SUM(t.num_sales) as r12_sum
from rmy join
     t
     on rmy.rep_id = t.rep_id and
        t.year(month_yr)*12 + month(month_yr) between rmy.yrmon - 11 and rmy.yrmon
group by rmy.rep_id, rmy.yr, rmy.mon
order by 1, 2, 3  

This hasn't been tested, so it may have syntactic errors. Also, it doesn't convert the year/month combination back to a date, leaving the values in separate columns.

Answer 5

Here is one solution:

SELECT 
  a.rep_id
  ,a.month_yr
  ,SUM(b.R12_Sum) AS R12_TTM
FROM YourTable a
  LEFT OUTER JOIN YourTable b 
    ON a.rep_id = b.rep_id
    AND a.month_yr <= b.month_yr
    AND a.month_yr >= DATEADD(MONTH, -11, b.month_yr)
GROUP BY
  a.rep_id
  ,a.month_yr