March 12, 2012 at 9:13 pm
Comments posted to this topic are about the item Get month names ordered using recursion
March 12, 2012 at 9:47 pm
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
March 12, 2012 at 10:13 pm
Thanks for sharing. Easy to use.
Thanks
March 13, 2012 at 12:23 am
A recursive CTE is also row by row.
March 13, 2012 at 12:26 am
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
March 13, 2012 at 12:28 am
Yes, recursive CTE is also row by row. When you are not joined any tables then recursive CTE will restrict upto 100 rows.
March 13, 2012 at 12:46 am
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. 🙂
March 13, 2012 at 1:01 am
It is Interesting, Idea is to make the SQL script very simple and reusable. Thanks for your comments.
March 13, 2012 at 1:02 am
You are welcome..
March 13, 2012 at 1:09 am
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.)
March 13, 2012 at 1:36 am
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
March 13, 2012 at 1:47 am
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
March 13, 2012 at 2:03 am
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
March 13, 2012 at 2:06 am
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
March 13, 2012 at 2:58 am
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