SQL Server 2008 Version of rows between... preceding and current row Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008



As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + Jan to July 2017 = 4625

Similarly, the SUM column for August 2017 is equal to the following: Sep to Dec 2016 + Jan to August 2017 = 4625



How can I automate this from month to month?

I understand that this can be done using window functions in SQL Server 2012 onwards but I have to modify this query to work in SQL Server 2008

select year(date), month(date),
   sum(sum(numbers)) over (order by year(date), month(date) rows between 11 preceding and current row) as prev_12_sum
 from t
 group by year(date), month(date)
 order by min(date);


I appreciate any help I can get.
Here's one method (that works in versions 2012+ unfortunately):

with 
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(sum(numbers)) over (order by year(date), month(date))
            as running_total,
        row_number() over (order by year(date), month(date))
            as rn,
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    t.running_total - coalesce(p.runinng_total, 0)
        as total_12_months
from
    cte as t
    left join cte as p
    on t.rn - 12 = p.rn ;


This will work only if there are no months with gaps (without any entry). 



Another try, for older versions:

with 
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(numbers) as sum_numbers
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    sum(p.sum_numbers) as total_12_months
from
    cte as t
    left join cte as p
    on p.year = t.year - 1 and p.month &gt; t.month
    or p.year = t.year     and p.month &lt;= t.month
group by
    t.year, t.month ;