Get month names ordered using recursion

  • Tony Palmeri (3/13/2012)


    I'm on an older version of SQL Server, so maybe what I am about to suggest already exists in a newer version. ..... the former.

    Phil Factors example above should work in every version back to 7 and I am pretty sure 6.5. Might need a small tweak but don't think so.

  • You can use the ROW NUMBER of any large table as a list of integers. You can probably rely on the sysobjects table being there.

    select DATENAME(MONTH,DATEADD(MONTH,ROWNUM,0)- 1)[MonthName] from

    (select row_number() over (order by name) as ROWNUM from sysobjects) A

    where ROWNUM < 13

  • Jeff Moden (3/13/2012)


    david.howell (3/13/2012)


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

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

    Read the following article for the reason why that's not such a good thing to say especially in this case. And, no, the performance problems associated with "counting rCTEs" aren't documented in BOL.

    {EDIT} Sorry, fogot the link. Here it is...

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    Agreed.

    I was being kind.

    I did mention that it could get out of hand e.g. in a correlated subquery, but better not to encourage this even in trivial situations.

    Yes I've read your article before 🙂 Thanks.

  • I have a question. When you use "ROW_NUMBER" against an actual table, doesn't the query processor actually have to physically access that table, maybe do some disk reads, even if you aren't explicitly using any of the fields from that table? This is what bothers me about that 'alternative' solution to a TallyTable. Not that a TallyTable is any better. I still don't understand why T-SQL doesn't already have a built-in fuction that essentially creates an in-memory TallyTable on the fly, efficiently and algorithmically. I still like my suggestion earlier, a Table-Valued function built-in to SQL server that has all the features of a traditional ForNext loop. I say "built-in" to SQL Server, only because I am guessing that a UserDefined function might be terribly inefficient for some reason. If SQL Server natively had the algorithm to generate such a table efficiently in-memory on the fly without requiring Disk Access, it seems the ideal solution to me, which obviously has many applications.

  • Qualicon (3/13/2012)


    TIP SSRS 2008 R2: Make a shared dataset from your query so you can use it any reports that needs the drop down list for ordered months....

    You can, although I've never been a fan. I prefer to keep in in the database, whether it's a stored proc or a view or a table.

    The main argument for doing this is being able to see all the code in one place. Then you can query across all the code and find dependencies which will save you from modifying the database detrimentally.

  • No offense but this should be an example of how NOT to do things in SQL server. Before writing anything we should make sure that we actually understand what we are talking about. There are literally tons of ways to do this without the need of CTE not me mention a recursive one.

    CTE is nice at times but we have to keep in mind that it doesn't work on all platforms so I would avoid it and use the old good derived table or result set whatever you want to call it:

    select

    q.*

    (

    select * from...

    ) q

    I know that CTE has it's own advantages but should be used where there is no other way of doing things. It is excellent for recursive but in this case is totally counter indicated.

    Sorry for being blunt.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • david.howell (3/13/2012)


    Qualicon (3/13/2012)


    TIP SSRS 2008 R2: Make a shared dataset from your query so you can use it any reports that needs the drop down list for ordered months....

    You can, although I've never been a fan. I prefer to keep in in the database, whether it's a stored proc or a view or a table.

    The main argument for doing this is being able to see all the code in one place. Then you can query across all the code and find dependencies which will save you from modifying the database detrimentally.

    I thought it interesting that though the original article was in regard to creating drop down lists for user selection parameters in reports, nobody had mentioned using report parts before this. You can use whichever list-getting method gives you joy and plop it in a Parameter report part saved to the report server. If you find that another querying method works better later, just update the part. If the Part uses a Stored Procedure, even better. Magically dynamic.

    For this specific purpose, it might not even be that awful to *ummmm bite my tongue* hardcode the 12 months as available values for a Parameter Part. [ Assuming that the months as we know them don't change anytime soon.... let us not forget the Mayan calendar "ends" this year... 😛 ]

    hrmmmm... what happens to report parts in case of armageddon?

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • Burninator (3/13/2012)


    david.howell (3/13/2012)


    Qualicon (3/13/2012)


    TIP SSRS 2008 R2: Make a shared dataset from your query so you can use it any reports that needs the drop down list for ordered months....

    You can, although I've never been a fan. I prefer to keep in in the database, whether it's a stored proc or a view or a table.

    The main argument for doing this is being able to see all the code in one place. Then you can query across all the code and find dependencies which will save you from modifying the database detrimentally.

    I thought it interesting that though the original article was in regard to creating drop down lists for user selection parameters in reports, nobody had mentioned using report parts before this. You can use whichever list-getting method gives you joy and plop it in a Parameter report part saved to the report server. If you find that another querying method works better later, just update the part. If the Part uses a Stored Procedure, even better. Magically dynamic.

    For this specific purpose, it might not even be that awful to *ummmm bite my tongue* hardcode the 12 months as available values for a Parameter Part. [ Assuming that the months as we know them don't change anytime soon.... let us not forget the Mayan calendar "ends" this year... 😛 ]

    hrmmmm... what happens to report parts in case of armageddon?

    haha

    I'm pretty sure report parts with hard-coded queries are the first sign of the apocolypse, and they will survive it like cockroaches survive a nuclear holocaust, along with all the other magic numbers scattered throughout the global code base.

    Meanwhile if there really is only one cycle of the Mayan calendar then I will be on a boat in the middle of a lake, with a bottle of Tequila watching the fireworks.

  • Something like this might help

    declare @t table(c varchar(21))

    insert @t (c)

    select 'October' union select 'December' union select 'November' union select 'September' union

    select 'May' union select 'June' union select 'July' union select 'August' union

    select 'April' union select 'March' union select 'February' union select 'January'

    select *

    from @t

    order by

    charindex(c+'*','January*February*March*April*May*June*July*August*September*October*November*December*')

  • The spt_values table is a great source of number sequences from 0 to 2048, the CTE is overkill in this case in my opinion.

    SELECT MoNum = number,

    MonthName = DATENAME(MONTH, DATEADD(MONTH, number - 1, 0))

    FROM master.dbo.spt_values

    WHERE type = 'P' AND number BETWEEN 1 AND 12

    You can also get the month names directly from the syslanguages table, you just have to parse the comma-separated list.

    SELECT MoNum = ROW_NUMBER() OVER (ORDER BY number),

    MonthName = SUBSTRING(list, number, CHARINDEX(',', list, number) - number)

    FROM (

    SELECT list = ',' + months + ','

    FROM sys.syslanguages

    WHERE lcid = SERVERPROPERTY('LCID')

    ) ml

    INNER JOIN master.dbo.spt_values v ON v.type = 'P' AND v.number BETWEEN 2 AND LEN(ml.list)

    WHERE SUBSTRING(ml.list,number-1,1) = ','

  • Here:

    select

    N+1 as month_nr,

    datename(mm,(dateadd(mm,N,'2012-01-01'))) as month_name

    from

    (

    select 0 as N union

    select 1 as N union

    select 2 as N union

    select 3 as N union

    select 4 as N union

    select 5 as N union

    select 6 as N union

    select 7 as N union

    select 8 as N union

    select 9 as N union

    select 10 as N union

    select 11 as N

    ) nrs

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Make it a view to be easier:

    create view vwMonths as

    select

    N+1 as month_nr,

    datename(mm,(dateadd(mm,N,'2012-01-01'))) as month_name

    from

    (

    select 0 as N union

    select 1 as N union

    select 2 as N union

    select 3 as N union

    select 4 as N union

    select 5 as N union

    select 6 as N union

    select 7 as N union

    select 8 as N union

    select 9 as N union

    select 10 as N union

    select 11 as N

    ) nrs

    GO

    select * from vwMonths

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Honestly the article is good even though I would not use this method for what they are trying to do. But just like a cursor understanding how this works can offer benefit somewhere else. If we are talking efficiency versus this I would not even bother with a sql query, instead I would opt for setting up a static parameters list for this scenario using the following method for label and value

    label =MonthName(1,False)

    value =1

    and do one for each month. Then I get a full list and it can function across laguages still. Lisa presents a uniquer need but I am sure it could be managed via code but I haven't got a moment to look at how that could be better done.

  • andrew.diniz (3/13/2012)


    abhishekgupta109 (3/13/2012)


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

    Why not? Many users would prefer to select an argument and see the report return nothing than not see the argument at all. That said, the point is moot as the decision will most likely be driven by user requirements.

    (snip)

    Agree wholeheartedly. Users expecting to see a list of months generally expect to see 12 of them. Moreover, you could use this with a LEFT JOIN to guarantee to return a rowset with 12 rows; people expect to see 12 monthly entries per year. If some of those months have no data or are in the future, users expect to get a NULL in those rows, rather than no row at all; it is reassuring that the database looked for data and found none.

  • Burninator (3/13/2012)


    hrmmmm... what happens to report parts in case of armageddon?

    Heh... if you've written the code the way most managers want it, it will be reported as "on time and under budget". 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 76 through 90 (of 129 total)

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