Querying average and rolling 12 month average

Question

I want to be able to find out the average per month and rolling average over the last 12 months of a count for the number of changes per customer.

SELECT 
    crq_requested_by_company as 'Customer',
    COUNT(crq_number) as 'Number of Changes'
FROM 
    change_information ci1
GROUP BY 
    crq_requested_by_company

At the moment I am just doing the count of the total and my results look like this

crq_requested_by_company    count   
A                             4
B                             2
C                             2269
D                             7696
E                             110
F                             91
G                             33

The date column I will be using is called 'start_date'.

I assume GETDATE() will be needed to work out the rolling average for the last 12 months.

Additional info after comments:

Using the code

;WITH CTE as
(
SELECT 
crq_requested_by_company as Customer,
COUNT(crq_number) Nuc,
dateadd(month, datediff(month, 0, crq_start_date),0) m
FROM 
change_information ci1
WHERE
crq_start_date >= dateadd(month,datediff(month, 0,getdate()) - 12,0)
GROUP BY 
crq_requested_by_company,
datediff(month, 0, crq_start_date)
)
SELECT 
Customer,
avg(Nuc) over (partition by Customer order by m) running_avg,
m start_month,
avg(Nuc) over (partition by Customer) simply_average
FROM
CTE
ORDER BY Customer, start_month

This gives the results

Customer    running_avg start_month      simply_average
A                8      01/01/2016 00:00 13
A                10     01/02/2016 00:00 13
A                10     01/03/2016 00:00 13
A                11     01/04/2016 00:00 13
A                14     01/05/2016 00:00 13
A                13     01/06/2016 00:00 13
B                1      01/01/2016 00:00 1
C                3      01/01/2016 00:00 2
C                3      01/02/2016 00:00 2
C                2      01/03/2016 00:00 2
C                2      01/04/2016 00:00 2
C                2      01/05/2016 00:00 2
C                2      01/06/2016 00:00 2

It needs to look like this so the average of the results above - the average of the 6 months above (I only currently have 6 months of data and needs to be 12 eventually)

Customer   avg_of_running_avg
A          11
B          1
C          2

Answer 1

Try this, it should work for sqlserver 2012 using running average:

;WITH CTE as
(
SELECT 
    crq_requested_by_company as Customer,
    COUNT(crq_number) Nuc,
    dateadd(month, datediff(month, 0, start_date),0) m
FROM 
    change_information ci1
WHERE
  start_date >= dateadd(month,datediff(month, 0,getdate()) - 12,0)
GROUP BY 
    crq_requested_by_company,
    datediff(month, 0, start_date)
)
SELECT 
  Customer,
  avg(Nuc) over (partition by Customer order by m) running_avg,
  m start_month,
  avg(Nuc) over (partition by Customer) simply_average
FROM
  CTE
ORDER BY Customer, start_month