SQL Query for SSRS parameter - 12 month rolling

Posted on 2009-04-30
Last Modified: 2012-05-06
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?
Question by:xtrout
    LVL 9

    Expert Comment

    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
    	declare @Count int
    	set @Count = 0
    	WHILE @Count < 12 
    		insert into @Months values(DATENAME(month, DateAdd(month, - @count, GETDATE()) ) + ' ' + DATENAME(year, DateAdd(month, - @count, GETDATE())))
    		set @Count = @Count + 1

    Open in new window


    Author Comment

    Would this be considered a scalar valued function. Not used to creating functions so I thought I would make sure.

    Accepted Solution

    I found a way to do it...

    with cte_last_12_months (CurrDate, MonthLabel)
         --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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now