CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlcalculated-columns
Július Marko
Július Marko

Reputation: 1296

How to do SUM over month columns in past year

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 18.07.2017, I want SUM from 18.07.2016 (from july 2016 till july 2017).

I have no idea how to do it effectively.

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Unpivot the data and then do the calculation:

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());

Upvotes: 1

Related Questions