Query to create 12 month aggregate for 3M, 6M totals

1

Hello. I am using SQL Server 2008 R2. I'm trying to create a query that aggregates by 3M, 6M, 9M, 12M totals. Here is some sample data:

create table invoice (store int, invoice_date datetime, customer int, is_repeat varchar(1));

insert into invoice (store, invoice_date, customer, is_repeat)
values (1, '2015-10-05', 1, 'N'),
(1, '2016-03-04', 1, 'Y'),
(1, '2016-02-07', 1, 'Y'),
(1, '2015-08-03', 2, 'Y'),
(2, '2015-12-01', 3, 'Y'),
(2, '2016-02-16', 4, 'Y'),
(2, '2015-06-11', 3, 'Y'),
(2, '2015-09-18', 4, 'Y');

The query needs to give a 3M, 6M, etc count of 'Y' values and aggregate it by store, month, and year. For example, there should be a record for Store 1 for March 2016 that totals the number of 'Y' values between 12/1/2015 and 02/29/2016 and puts that value in the 3M column. Then also sum the values between 9/1/2015 and 02/29/2016 and put those values in the 6M column.

Here's an example of the what the query results should be for the above data:

STORE     MONTH     YEAR     3M_REPEATS     6M_REPEATS
-----     -----     ----     ----------     ----------
  1         2       2016         0              2
  1         3       2016         1              2
  2         2       2016         1              3
  2         3       2016         2              3

I'm trying to find an elegant solution but I'm not sure if I should use a CTE, or PIVOT, or OVER(PARTITION BY), or just standard GROUP BY clauses.

I ultimately just need the last 12 months in this table, so there would only be 12 records in this table. For example, if current month/year is 3/2016, it would have 2/2015 - 3/2016 and the totals for each month.

2

I would do it this way:

with tallyno5(n)
  as (select 0 from (values(0),(0),(0),(0),(0)) as tally(n))

    ,tallyno25(n)
  as (select row_number() over(order by (select null))-1
        from tallyno5 as a
             cross apply tallyno5 as b
     )

    ,tallymonth(n)
  as (select datediff(month,0,current_timestamp)-n
        from tallyno25
       where n<=23
     )

    ,inv(store,mn,c)
  as (select store
            ,datediff(month,0,invoice_date)
            ,count(*)
        from invoice
       where is_repeat='Y'
       group by store
               ,datediff(month,0,invoice_date)
     )

    ,inv_pivot(store,mn,m3,m6,m9,m12)
  as (select i.store
            ,m.n
            ,sum(case
                    when i.mn>=m.n-2
                     and i.mn<=m.n
                    then i.c
                    else 0
                 end
                ) as m3
            ,sum(case
                    when i.mn>=m.n-5
                     and i.mn<=m.n
                    then i.c
                    else 0
                 end
                ) as m6
            ,sum(case
                    when i.mn>=m.n-8
                     and i.mn<=m.n
                    then i.c
                    else 0
                 end
                ) as m9
            ,sum(case
                    when i.mn>=m.n-11
                     and i.mn<=m.n
                    then i.c
                    else 0
                 end
                ) as m12
        from tallymonth as m
             inner join inv as i
                     on i.mn>=m.n-11
                    and i.mn<m.n+1
       group by i.store
               ,m.n
     )
select store
      ,year(dateadd(month,mn,0)) as y
      ,month(dateadd(month,mn,0)) as m
      ,m3
      ,m6
      ,m9
      ,m12
  from inv_pivot
 where mn>=datediff(month,0,current_timestamp)-13
 order by store
         ,mn
;