CODEWITHSUNDEEP
datereporting-servicessql-server-2012
Joe Resler
Joe Resler

Reputation: 101

Syntax for column values for same period of last year in SSRS report

I am generating a report where I SUM price and cost values per month. I would like to show the same defined time period - 1 year. I'm not clear how to nest this. Would I use a case when statement?

My terrible example. 

select month, cost, price from table1 where (define months here in SSRS parameter filter)

I'd like to see:

select month, cost, price, lastyearcost, lastyearprice FROM table1 where (define months here in SSRS parameter filter)

I know that I should be using some variant of GETDATE() -1, but will this include the data range parameters passed when running the report? How do I select the column of cost apply the date filter and then get the result of cost for that period last year?

Hopefully that makes sense?

Upvotes: 1

Views: 724

Answers (1)

S3S
S3S

Reputation: 25142

One way is to use DATEPART() with the correct nesting and pairing.

select 
    month, 
    cost, 
    price, 
    lastyearcost, 
    lastyearprice 
FROM table1 
where 
    --this is limiting the data to the year passed in, and the previous year
    (datepart(year,DateColumn) = @YearParam or datepart(year,DateColumn) = @YearParam-1)
    and 
    --this is limiting the months to the two parameters you pass in as integers
    (datepart(month,DateColumn) >= @minMonthParam and datepart(month,DateColumn) <= @maxMonthParam)

TEST DATA

See the DEMO HERE

declare @table1 table (DateColumn date)
insert into @table1 (DateColumn)
values
('1/1/2016'),
('2/1/2016'),
('3/1/2016'),
('4/1/2016'),
('5/1/2016'),
('6/1/2016'),
('7/1/2016'),
('8/1/2016'),
('9/1/2016'),
('10/1/2016'),
('11/1/2016'),
('12/1/2016'),
('1/1/2017'),
('2/1/2017'),
('3/1/2017'),
('4/1/2017'),
('5/1/2017')

declare @YearParam int = 2017
declare @minMonthParam int = 2
declare @maxMonthParam int = 5

select 
    DateColumn
FROM @table1 
where 
    (datepart(year,DateColumn) = @YearParam or datepart(year,DateColumn) = @YearParam-1)
    and 
    (datepart(month,DateColumn) >= @minMonthParam and datepart(month,DateColumn) <= @maxMonthParam)

Upvotes: 1

Related Questions