March 13, 2012 at 11:01 am
This is a good situation for using a Tally table. Its fast, simple, and a reusable generic tool. Tally tables are not recursive and most importantly are usable in SET operations.
Select xNum, datename(month,xNum) From mydb.dbo.Tally Where xNum<12
A Tally table contains just one primary key column of integers:
xNum
------
1
2
3
4
...etc.
Though of course you can make it more complex if you need it to be, including negatives, decimals, additional columns, and so forth. I realize that the original code was intended to avoid using tables - my response is that you should NOT avoid tables - they are a core component of SET oriented coding. If you are in an environment where you can define your own procs, then you can add a Tally table - or better yet, your own reference DB that includes all your standard toolset for use across all other DBs - a count table, custom date and search functions, and so on.
March 13, 2012 at 11:02 am
Although I agree with you in keeping the script simple, I don't agree with the dynamic vs static. Month is one of those things you can garentee to be static over time. But maybe I'm missing something... :w00t:
March 13, 2012 at 11:03 am
Antares686 (3/13/2012)
Just because I was in the mood for a little fun but another option is to use the syslanguages table to get the information like you want. There is a months field and you could use it to get alternate languages by supplying a different id if you need. The field is a comma delimited list but you can break it up into rows using XML like so. May not be the most efficient but is another option.
I like this. But if we're going to internationalize it (good idea!) let's also allow for different ordering of the months, I'm surprised nobody mentioned this as a difficulty -- considering the original problem being covered by the article.
Just add a second parameter to handle this possibility, for example like this:
,@FirstFiscalMonth tinyint = 1
... and you've got something I personally need all the time:
DECLARE @x XML, @offset as tinyint = @FirstFiscalMonth-1
SET @x = CAST((select replace((select months from master.sys.syslanguages
where langid = @LangID for xml path),
',','</months></row><row><months>')) AS XML)
SELECT Month_Number,
case when ( Month_Number - @offset) between 1 and 12 then
Month_Number - @offset
else (Month_Number - @offset) + 12
end as FiscalMonth_Number,
Month from
(SELECT
row_number() over (order by t.c) Month_Number,
t.c.value('.','varchar(50)') as Month
FROM
@x.nodes('row/months') t(c)
) xx order by 2
... I *think* this would work.
>L<
March 13, 2012 at 11:06 am
SELECT 'January', 1
UNION ALL
SELECT 'February', 1....
I could be wrong here, but this probably would do the same thing with a lot less processing since you don't call and functions....cheers
March 13, 2012 at 11:26 am
Getting a sorted month list, starting from any arbitrary date, is much easier using a Tally table, because calendars are already cyclical. For example, to pull the next 12 months:
Select t.N as MonthNum, DATENAME(Month,Dateadd(month,N,getdate())) as MonthName
From Tally t
Where t.N < 13
Its easy enough to replace getdate() with your initial fiscal month, or any other context driven date.
March 13, 2012 at 11:39 am
The original code that was presented will work for any language setting. That's important. Scooping out the contents of sys.syslanguages will only work if you do it according to the current language setting, but it isn't a pretty solution.
The CTE solution is slower than a simple approach, but in my benchmarks it is only 50% more. I was surprised to find that an approach like this ...
[font="Courier New"]SELECT
f.number + g.number,
DATENAME(MONTH, DATEADD(MONTH, f.number + g.number, 0) - 1) AS MonthName
FROM (SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3) f (number)
CROSS JOIN
(SELECT 0
UNION ALL SELECT 3
UNION ALL SELECT 6
UNION ALL SELECT 9) g (number)
ORDER BY f.number + g.number
[/font]
...was almost exactly the same speed as the tally table approach.
Best wishes,
Phil Factor
March 13, 2012 at 11:48 am
Phil Factor (3/13/2012)
The original code that was presented will work for any language setting. That's important. Scooping out the contents of sys.syslanguages will only work if you do it according to the current language setting, but it isn't a pretty solution....
I concur on this item. My post was not intended as a final solution, just an alternative in the conversation.
March 13, 2012 at 12:08 pm
Phil Factor (3/13/2012)
The original code that was presented will work for any language setting. That's important. Scooping out the contents of sys.syslanguages will only work if you do it according to the current language setting, but it isn't a pretty solution.
Sorry, let me be clear:
1 - the thing I liked about the syslanguages solution, as written, was that you could pass in any language ID as a parameter, -- Phil, you're saying this wouldn't work? There are lots of things I don't understand about current language settings in SQL 🙂 -- can you add some info here? Thanks!
2 - the thing I *added* was fiscal first month handling. It's a separate issue for me. Frankly I think that needs to be added to *any* version of the code in this discussion before I could use it.
Look: the original article was about "ordering month names properly" for reporting purposes. For me, it's never usable if it assumes that the physical month order and the display month order are always the same.
IOW, localization or not, Phil, I'd still emend *your* code as follows:
DECLARE @FirstFiscalMonth as tinyint = 1
-- on the assumption this is passed in somewhere
-- and is not always 1
DECLARE @offset as tinyint = @FirstFiscalMonth-1
select MonthNumber,
case when ( MonthNumber - @offset) between 1 and 12 then
MonthNumber - @offset
else (MonthNumber - @offset) + 12
end as FiscalMonthNumber,
MonthName
from
(SELECT
f.number + g.number as MonthNumber,
DATENAME(MONTH, DATEADD(MONTH, f.number + g.number, 0) - 1) AS MonthName
FROM (SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3) f (number)
CROSS JOIN
(SELECT 0
UNION ALL SELECT 3
UNION ALL SELECT 6
UNION ALL SELECT 9) g (number)
) xx
order by 2
Maybe "fiscal month" is the wrong label, to state the general case, but I can't believe I'm the only person who has this problem with the various solutions presented...
>L<
March 13, 2012 at 12:26 pm
mtassin (3/13/2012)
alfredoapereira (3/13/2012)
but you must have a tally table in your database. and you may not need a calendar table.Not true... the Tally CTE outperforms the Tally Table. But even it isn't recursive.
Ah... it depends by what you mean by "outperforms" The Tally CTE will generally not produce any reads but will generally be slower than the Tally table. Not by very much, though (talking milliseconds for less than 8k). Either solution is fine in my book.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2012 at 12:32 pm
because the end user might be confused as to why 'March' (for example) is not in the dropdown list... it could be a feature or a needless helpdesk ticket being sent to IT depending upon the user...
March 13, 2012 at 12:37 pm
Jeff Moden (3/13/2012)
mtassin (3/13/2012)
alfredoapereira (3/13/2012)
but you must have a tally table in your database. and you may not need a calendar table.Not true... the Tally CTE outperforms the Tally Table. But even it isn't recursive.
Ah... it depends by what you mean by "outperforms" The Tally CTE will generally not produce any reads but will generally be slower than the Tally table. Not by very much, though (talking milliseconds for less than 8k). Either solution is fine in my book.
Wha? One day I will stop being the learner... this is not the day... I could have sworn you stated at some point that the Tally CTE was actually better performing than the table based one.
Edit: It was Wayne and my disucssion in the Tally-O thread... which weirdly enough I received an upate on. 🙂
Either case, both beat using recursion for the sake of recursing.
March 13, 2012 at 12:51 pm
IOW, localization or not, Phil, I'd still amend *your* code as follows:
Please feel free. I was just taking the spec from the original version. My point about using the sys.syslanguages table to get the information was that it should take the current language setting if it is to to keep to the functionality of the original routine. I have to admit to a built-in aversion to accessing sys.syslanguages if the information can be fetched more conventionally. It is not a good place for a relational database person.
Best wishes,
Phil Factor
March 13, 2012 at 1:17 pm
Regarding the display order of months, I was trying to say that the original spec was (IMHO) really flawed.
Regarding the language thing... while it wasn't a flaw in the spec per se, I think extending the idea to a localized list is a good idea. I am glad that (you seem to be saying here) it would actually work, even if it isn't ideal.
And I don't really have a more conventional way to get this information from within an RDL context. I'll have to think about that!
FWIW, typically, there's already at least a three-part effort to localizing a report exposed to users:
* -- the application itself [IOW, the web page]
* -- the reportviewer control surface
* -- the contents of the report, which sometimes includes:
* -- localization of the visible parameter values
... anything I can do to make the 4th part less of a PITA, I want to do.
>L<
March 13, 2012 at 1:27 pm
I'm on an older version of SQL Server, so maybe what I am about to suggest already exists in a newer version. It would be great if SQL natively supported a built-in table-valued function to generate a range of integers in the same manner of a For-Next loop. Something like: ForNext(x,y,s) where x and y are the starting and ending numbers, respectively, and s is the 'step' value (defaults to '1'). If the SQL query processor just simply did this algorithmically, efficently in memory, I would think it would be FAR more efficient than using a "Tally Table" (which has to be read from disk, and my be corrupt, or may not have the full range of numbers needed), and probably more efficient than a recursive CTE.
Applying to the current problem:
SELECT MonthName(MonthNumbers.Number)
FROM ForNext(1,12) AS MonthNumbers
ORDER BY MonthName(MonthNumbers.Number)
[I'm assuming the existence of a function called MonthName() here].
Although, I would NEVER do this in SQL server. Why on earth would you ask SQL server to do this (for the sake of populating a control in a user interface) when there could be code in the User-Interface to do it?
A lot of the solutions I saw posted here show a complete disregard for efficiency, or what the query processor actually has to *do* (under the hood) to accomplish it. I don't give anyone credit for being "clever" - if there is really some other obvious, brute force, sometimes uglier / sometimes more elegant solution which is far more efficient. Our purpose is to provide our customers with fast, efficent, reliable code, not impress each other with a cool trick. I'm not slamming anyone in particular here, but I have to say that all-too-often the motive seems to be more the latter than the former.
March 13, 2012 at 2:47 pm
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....
Viewing 15 posts - 61 through 75 (of 129 total)
You must be logged in to reply to this topic. Login to reply