# 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