SQL Server 2008 – Version of Rows Between Preceding and Current Row

sql serversql-server-2008sql-server-2008-r2window functions

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008

enter image description here

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

enter image description here

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.

Best Answer

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 > t.month
    or p.year = t.year     and p.month <= t.month
group by
    t.year, t.month ;
Related Question