March 13, 2012 at 3:44 pm
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.
March 13, 2012 at 4:07 pm
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
March 13, 2012 at 4:42 pm
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...
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.
March 13, 2012 at 5:07 pm
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.
March 13, 2012 at 5:09 pm
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.
March 13, 2012 at 5:31 pm
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
March 13, 2012 at 6:19 pm
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?
March 13, 2012 at 6:58 pm
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.
March 13, 2012 at 9:02 pm
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*')
March 14, 2012 at 6:55 am
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) = ','
March 14, 2012 at 10:38 am
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
March 14, 2012 at 10:40 am
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
March 14, 2012 at 10:45 am
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.
March 14, 2012 at 4:52 pm
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.
March 14, 2012 at 9:49 pm
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
Viewing 15 posts - 76 through 90 (of 129 total)
You must be logged in to reply to this topic. Login to reply