Link to home
Start Free TrialLog in
Avatar of xtrout
xtrout

asked on

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?
Avatar of ezraa
ezraa
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

Avatar of xtrout
xtrout

ASKER

Would this be considered a scalar valued function. Not used to creating functions so I thought I would make sure.
ASKER CERTIFIED SOLUTION
Avatar of xtrout
xtrout
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial