Get month names ordered using recursion

  • Comments posted to this topic are about the item Get month names ordered using recursion

  • Ummmm. Recursive CTE, why?

    Single SELECT statement based on AdventureWorks:

    SELECT DISTINCT DATENAME(mm,OrderDate), MONTH(OrderDate)

    FROM Sales.SalesOrderHeader

    WHERE OrderDate BETWEEN '1/1/2006' and '12/31/2006'

    ORDER BY MONTH(OrderDate)

    Michael Hotek

  • Thanks for sharing. Easy to use.

    Thanks

  • A recursive CTE is also row by row.

  • Good Question!! This CTE does not depends on any tables. Think of the environment that do not have any tables which has the date for the consecutive month and you need to accomplish the requirements.In this case you cannot depend on any tables. Also keep performance and re-usability in mind.

    Thanks,

    Karthik

  • Yes, recursive CTE is also row by row. When you are not joined any tables then recursive CTE will restrict upto 100 rows.

  • The server wide maxrecursion default is 100, irrespective of whether you join to a table or not.

    BOL: http://msdn.microsoft.com/en-us/library/ms175972.aspx

    More dangerous is that "if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created" effectively the same as setting maxrecursion to 0, so good practice would be to specify the option for maxrecursion.

    Anyway, what you are doing is tiny - only 12 rows so none of this matters.

    Unless it ends up in a correlated subquery which of itself would be bad.

    If you are looking at something reusable then you may as well have a table with the monthnames and numbers, assuming the bizarre situation that you have a database with no dates in it. Given this is a paremeter for a report, then logically there must be dates in the data for it to be usable.

    In most SSRS parameter situations, it's even more robust to source the options from the data itself.

    Thanks for the article, it prompted me to read the rCTE BOL entry. 🙂

  • It is Interesting, Idea is to make the SQL script very simple and reusable. Thanks for your comments.

  • You are welcome..

  • Interesting one....

    but why would you display a month name, if we do not have any data for it. (just for the sake of displaying a blank report.)

  • If you wanted to have something to populate a drop down, without having any data as the basis for that drop down, you can just as easily hard code 12 SELECT statements with UNIONs and never have to go to the sledgehammer of an approach of using a recursive CTE. This is just using a recursive CTE for the sake of using one, not that it provides any benefit.

    Besides, it doesn't make any sense to show a drop down in a report populated without any basis for the actual data that the report is going to display. That just leads to a bunch of frustrated users. Are you going to let them select 2012 for a year and then show them a drop down with all 12 months in it even though you probably don't have any data for 9 of those 12 months at this point, causing users to get blank report after blank report until they finally guess which month and year combination is valid?

    Even if you showed them prompts for a year separate from a month, you are just creating a very good argument for having a calendar table so that you don't have to compute date parts on the fly every time you run a query.

    Michael Hotek

  • A simple SELECT that should work on every SQL Server database:

    SELECT TOP 12

    MonNum= ROW_NUMBER() OVER (ORDER BY sc1.NAME desc)

    ,MonthName= DATENAME(MONTH,DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY sc1.NAME desc),0)- 1)

    FROM Master.dbo.SysColumns sc1

    Although I'm with Michael on this one. A calendar table would make sense, or a SELECT on the fact data for the report, so that only dates are shown which have actual data.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Here MonthName is a parameter where i can select the month. For example if you get the details of sales for the month of Feb, then you can select the month Feb then click on view report.

    Thanks,

    Karthik

  • Krtyknm (3/13/2012)


    Here MonthName is a parameter where i can select the month. For example if you get the details of sales for the month of Feb, then you can select the month Feb then click on view report.

    Thanks,

    Karthik

    And what if February doesn't have any data?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I thoght I would come up with my own solution before reading the article. I think the recursive CTE you've come up with is interesting, but not realy necessary. The performance hit of pretty much any method is going to be minimal, so probably better to err on the side of keeping it simple.

    Anyway, my CTE solution was

    set dateformat dmy

    go

    ;with myCTE(monthnumber, monthname) as

    (

    select datepart(mm, '01/01/2012') as monthumber, datename(mm, '01/01/2012') as monthname

    union

    select datepart(mm, '01/02/2012'), datename(mm, '01/02/2012')

    union

    select datepart(mm, '01/03/2012'), datename(mm, '01/03/2012')

    union

    select datepart(mm, '01/04/2012'), datename(mm, '01/04/2012')

    union

    select datepart(mm, '01/05/2012'), datename(mm, '01/05/2012')

    union

    select datepart(mm, '01/06/2012'), datename(mm, '01/06/2012')

    union

    select datepart(mm, '01/07/2012'), datename(mm, '01/07/2012')

    union

    select datepart(mm, '01/08/2012'), datename(mm, '01/08/2012')

    union

    select datepart(mm, '01/09/2012'), datename(mm, '01/09/2012')

    union

    select datepart(mm, '01/10/2012'), datename(mm, '01/10/2012')

    union

    select datepart(mm, '01/11/2012'), datename(mm, '01/11/2012')

    union

    select datepart(mm, '01/12/2012'), datename(mm, '01/12/2012')

    )

    select monthname from myCTE order by monthnumber

Viewing 15 posts - 1 through 15 (of 129 total)

You must be logged in to reply to this topic. Login to reply