How to SUM over month columns in past year best way?

Viewed 85

I have following columns from Jan to Dec:

Year - Jan - Feb - Mar - (etc.) - Dec
----   ---   ---   ---            ---
2015   25    32    102            12
2016   30    40    50             60

How to effectively do SUM over past year? Let's say from GETDATE(), If today is 16.08.2017, I want SUM from 16.08.2016 (from august 2016 till august 2017).

I have following code:

select sum(val)
from t cross apply
     (values (t.year, t.Jan, 1),
             (t.year, t.Feb, 2),
             . . .
     ) v(yyyy, val, mon)
where yyyy * 100 + mon >= (year(getdate()) - 1) * 100 + month(getdate());

which works, but is there any way to do it without cross apply? (for instance: just where clause)

2 Answers
Related