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)