Július Marko Július Marko - 1 year ago 77
SQL Question

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

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)

Answer Source

how about something like below which uses UNPIVOT notation.

select sum(val)
from
(select * from t )s
unpivot
( val for month in ( Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec))up
where 
  cast(month+ cast(year as varchar(20)) as date) 
   between  dateadd(m,-12,getdate()) and getdate()

Live Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download