Get month names ordered using recursion

  • Krtyknm

    SSC Eights!

    Points: 884

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

  • Mike Hotek

    SSC-Addicted

    Points: 457

    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

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Thanks for sharing. Easy to use.

    Thanks

  • davoscollective

    SSCertifiable

    Points: 6355

    A recursive CTE is also row by row.

  • Krtyknm

    SSC Eights!

    Points: 884

    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

  • Krtyknm

    SSC Eights!

    Points: 884

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

  • davoscollective

    SSCertifiable

    Points: 6355

    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. 🙂

  • Krtyknm

    SSC Eights!

    Points: 884

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

  • Krtyknm

    SSC Eights!

    Points: 884

    You are welcome..

  • abhishekgupta109

    SSC Enthusiast

    Points: 109

    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.)

  • Mike Hotek

    SSC-Addicted

    Points: 457

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • Krtyknm

    SSC Eights!

    Points: 884

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • Eric Hobbs

    SSC Veteran

    Points: 218

    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 130 total)

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