# 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 > t.month
or p.year = t.year     and p.month <= t.month
group by
t.year, t.month ;
``````
• Thanks, this does not seem to work in SQL Server 2008, I get the following error: The Parallel Data Warehouse (PDW) features are not enabled. Nov 28, 2018 at 22:51
• My bad. Running totals with `SUM() OVER (ORDER BY ..)` was added in 2012, not 2008. Nov 28, 2018 at 22:59