• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1780
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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