2

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.

3

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. – ThatRiddimGuy Nov 28 '18 at 22:51
  • 1
    My bad. Running totals with SUM() OVER (ORDER BY ..) was added in 2012, not 2008. – ypercubeᵀᴹ Nov 28 '18 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.