• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1694
  • Last Modified:

SQL Query for SSRS parameter - 12 month rolling

I am trying to create a dataset to populate a parameter for a report. I basically need it to display a list of Month + Year for the past 12 month and have it roll.

I can make it pull the current month using....

SELECT     DATENAME(month, GETDATE()) + ' ' + DATENAME(year, GETDATE()) AS Month

This returns April 2009

But I need something to return

April 2009
March 2009
February 2009
etc.. back to April 2008

Any suggestions?
0
xtrout
Asked:
xtrout
  • 2
1 Solution
 
ezraaCommented:
Create the function below and then you can call it with this:

select * from Last12Months()

CREATE FUNCTION Last12Months () returns @Months table ( Month varchar(30)) as
BEGIN
	declare @Count int
	set @Count = 0
	WHILE @Count < 12 
	BEGIN
		insert into @Months values(DATENAME(month, DateAdd(month, - @count, GETDATE()) ) + ' ' + DATENAME(year, DateAdd(month, - @count, GETDATE())))
		set @Count = @Count + 1
	END;
	RETURN
END

Open in new window

0
 
xtroutAuthor Commented:
Would this be considered a scalar valued function. Not used to creating functions so I thought I would make sure.
0
 
xtroutAuthor Commented:
I found a way to do it...

;
with cte_last_12_months (CurrDate, MonthLabel)
as
(
     --anchor query 1
     select CurrDate = getdate(),
              MonthLabel = datename(month, getdate()) + ' ' + convert(varchar(4), year(getdate()))
     union all
     --recursive query
     select CurrDate = dateadd(month,-1,cte.CurrDate),
              MonthLabel = datename(month, dateadd(month,-1,cte.CurrDate)) + ' ' + convert(varchar(4), year(dateadd(month,-1,cte.CurrDate)))
     from cte_last_12_months cte
     where cte.CurrDate > dateadd(month,-12,getdate())
)
select * from cte_last_12_months
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now