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

Reputation: 1296

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)

Upvotes: 2

Views: 92

Answers (2)

Tony C
Tony C

Reputation: 51

Assuming all your column month names are 3 letters, you could also use dynamic SQL. Something like the following should work. 

DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE)
DECLARE @Year INT = DATEPART(YEAR, @CurrentDate)
DECLARE @PrevYear INT = @Year - 1
DECLARE @Month CHAR(3)

SET @Month = convert(char(3), @CurrentDate, 0)

BEGIN
EXEC('SELECT SUM(t.'+@Month+') FROM t WHERE Year >= ' + @PrevYear + ' AND Year <= ' + @Year)
END

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17146

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

Upvotes: 3

Related Questions