This is a challenge to Generate text formatted month calendars.
Howdy folks,
The requirements for this fun task state "using a single query". While this can certainly be setup using a single query, the required output cannot be achieved without 1) making a settings change in SSMS to supress the column header or 2) indulging in a little trickery to supress the column header using a different type of statment to go along with the single query.
So, let me come right out and ask,
1) Do those "other" types of statements that output to the text window without a header violate the rule a "single query" and...
2) (Shifting gears a bit) does the use of a UDF (which would seem incredibly useful here) also violate the rule of "single query"?
As a sidebar, I know you good folks are seriously behind on your evaluation of winners for these challenges but I think it would be really cool if you posted Brad's solution to this problem on the day after the submittal deadline. Thanks.
--Jeff Moden
Hi Jeff...
Sorry if there's confusion regarding the column header. You don't have to suppress that... the expected output was shown without one, but that doesn't mean you have to suppress it.
Note Rule #1: The resulting output is a single 31-character column called Calendar.
The query will be evaluated on accuracy of output (and efficiency in terms of Reads, CPU, Duration, etc). We don't care about the header.
In answer to your second question... no UDF's. Just a single SELECT statement (with CTE's and/or subquery-derived-tables if necessary). The solution that's submitted should be one and only one T-SQL command.
Finally, I have no problem posting my solution on the day after the challenge closes... I'm sure Jacob and Rui will read this and agree... I'll mention it to them offline as well.
One other suggestion... If you need a numbers table of some kind, just use master..spt_values like so:
select Number from master..spt_values where Type='P' and Number>0
--Brad
Jeff:
One more comment on evaluations. I've worked real hard over the last week to try to automate the evaluation process, so the winner announcements will be coming quicker and will "catch up" with the new challenges over the next couple of weeks.
@jeff,
As always, you have come up with an interesting idea. We can certainly publish Brad's solution right after the challenge closes.
Brad, David, Rui and Adam had worked really hard in setting up an automatic evaluation process which will speed up the evaluation process. They really did a great job, that only a SQL Expert like them could do :-). We will soon see the results in the form of faster evaluation and results.
Thanks Jeff, Brad, Rui, David and Adam
rgds
Jacob
Thanks, Brad,
That was the confusing part... Rule #1 said a "column called Calendar" but the output didn't have it. Thank you for the clarification.
>>>In answer to your second question... no UDF's. Just a single SELECT statement (with CTE's and/or subquery-derived-tables if necessary). The solution that's submitted should be one and only one T-SQL command.
Perfect.
>>>One other suggestion... If you need a numbers table of some kind, just use master..spt_values like so:
Heh... You talkin' to me? (Tally Table) Just kidding. ;-) I'm sure folks appreciate the hint but the wicked old DBA from the North sometimes won't let us hit on systems tables because it causes "extra reads" so I'll do what I always do... cheat the system. ;) ;) ;)
@Jacob and Brad,
Thanks for the info on the evaluation process. I know it's a bit tough to do manually. Are you folks still going to look at the code for oolies? Some of the folks have some pretty clever techniques that I wouldn't mind seeing.
On the same subject... I don't know how caught up you'll be by then but I thought publishing Brad's solution on the 15th would probably help stave off the wolves (especially since I'm wolf hungry for oolies ;-)). Thanks for that.
Keep up the good work, you guys. Brad, nice to see you here.
Brad,
Is the output that you showed will come as
same, if we choose the option "Results to Grid"??????
I wrote a query.. which is showing the same output that you asked, when i choose the option, "Results to Text".. but if i choose the option "Results to Grid", its not showing in proper format, although all the rows are of exactly 31 Characters....
@Jeff:
"Oolies"?? Once I evaluate the results and produce statistics for Reads, CPU, Duration, etc, I pass it on to Jacob and Rui and let them go through them and evaluate the styles and different approaches. So far, I haven't taken the time to look in depth myself, but I should take the time, because I like to see new approaches.
@Ramireddy:
If you choose "Results to Grid", it will come out in a proportional font and things won't line up. I would suggest doing "Results to Text" in order to check your data, but it doesn't really matter. Do whatever you need to do to verify you have the correct output. All that we're going to check is the content, character-by-character. When we evaluate it, the "Results To..." won't make any difference.
@Jeff,
the plan is to identify 'one' solution that shows a new/different approach. I am sure this is going to be a real tough job and will take us some time to get there.
Regards
jacob
Ok.. Thanks Brad.. I am going to submit my solution now...
If you select "Courier New" as the Grid font, you won't have such a problem with alignment.
@Brad:
"Oolie" is the U.S. Submariner's term for a trick method or relatively unknown fact not necessarily to be confused with trivia.
I have one final question before I submit my script and, yes, I did look in the submittal guide...
Since you guys are automating the evaluation process, are we supposed to include the sample data generation code in the script we submit or not? Either way, you might want to add that keynote to the submittal guide.
Good point about the grid font.
We are in the process of changing the submission guidelines, and one of the requirements that we're going to add is that sample generation code NOT be in the submitted script.
It's easier for now if you DO NOT include it.
Perfect. I've submitted my code. Thanks Brad.
People hav already begun to ask help fro this challenge on public news groups
microsoft.public.sqlserver.programming
"TSQL Challenge 18 - Generate text formatted month calendars"
Even this guy replied to this question in 2 forums.... Plamen Ratchev
with some queries....
:(
I must express this is the hardest Challenge every posted on this forum.
Many congrats to every one who is involved with this efforts.
Kind Regards,
Pinal
If we have submitted the solution before the changes to submission guidelines do we need to resubmit it ?
@Chris:
What changes are you referring to? If you submitted something that includes the code to create the sample data, that's okay... we'll take out that part before we put the solutions through automated testing... it's only future challenges where we will want the sample data generation gone.
If it's something else (non SET based, for example), then you can resubmit it.
@Peso wrote: "People hav already begun to ask help fro this challenge on public news groups"
What's the full link to that, Peter? It'll be fun to watch...
Here's the full link...
www.microsoft.com/.../default.aspx
You'll find the discussion topic easily enough from there. Unfortunately, Plamen, who is a T-SQL genius who really knows his stuff, was a little too helpful and posted multiple solutions.
I wouldn't worry too much - their solutions don't work...
...sorry posted about a different solution than that one Brad...the one on sqlmonster is just wrong.
Can you quickly check my solution? I want to make sure that I didn't break any rules.
There are a bunch where the author(s) say it's easy... but check them out in the GRID mode or look at the uderscores for the column in the TEXT mode... most of them violate the 31 character column width (by 8030 characters in the most "frequent" post I've seen by someone who thinks it's "easy"). They also don't have a clue as to how to format the header nor the "borders".
Heh... in other words... cheaters beware. ;-)
Thanks for the link, Peter.
@Brad,
Plamen is the one with the 8061 column width. Someone should tell him, "Must look eye." ;-)
@Art:
I can't check your solution... I actually don't have permission to view them yet at the Media Gallery. I'll see if Jacob or Rui can give me access.
The only #1 rule you have to worry about (aside from the specific ones listed for this challenge) is that your solution is a single T-SQL statement (that either begins with SELECT or WITH)... no DECLARE... no Temp Tables... no UDF's.
Hi Brad,
I have updated the permissions. I missed it when we moved the media libraries. sorry for that.
I got access to the Media Gallery, and your solution looks fine... just a single T-SQL statement (not counting the creation of the @t test table).
I didn't check it for accuracy... just appearance... you're good.
@Jacob: Thanks!
You learn something new every day...
I didn't know that you don't have a leap year on the century years unless it is evenly divisible by 400.
@GoBlando:
Perhaps I "gave away too much" by using February 1900 in the test data. I should have sneaked it into the "tricky" data evaluation part of the challenge.
I wonder if any chaos or arguments came about on March 1, 1900, or if any will occur in 2100. The "400 rule" is not one that is known by the average person on the street.
(Fun fact: If you go into Excel and add 1 day to the date of Feb28,1900, Excel will happily tell you that the result is Feb29,1900. Wrong-o!)
One IMPORTANT word of warning:
I have taken a quick look at many of the solutions submitted so far (Dec 1, 7:30pm GMT), and about 20-30% of them are NOT following the guidelines.
I've seen CURSOR-based solutions and I've seen local variables and/or local or temp tables DECLAREd or CREATEd.
The solutions should consists of a SINGLE T-SQL STATEMENT (that begins with SELECT or WITH)... nothing more. The ONLY extra code that is acceptable in the solution is the creation and population of the @t test data table that was provided in the challenge.
If you need to use a Numbers table of some kind, either use master..spt_values or just create a numbers table on the fly WITHIN A CTE. DO NOT use separate T-SQL statements (like a WHILE loop or a GO trick or anything else) to create a numbers table variable or temporary table.
If a solution is submitted using any local variables or temp tables or table variables (other than the test data table @t) or UDF's or SP's or CURSORs, they will have to be disqualified.
Thanks for this challenge.. it is the most interesting challenge posted so far but I also think it is the easiest of them all..
I will soon post my query as well ;-)
Jonathan
Regarding Table variables... the stipulation that these should not be used was not as clear in previous challenges. In #14, I used a table variable only to hold a numbers table. My solution is still otherwise set-based. I hope this will not cause my solution to be disqualified from that challenge.
@Ksuchlicki:
I looked it up, and your #14 solution was NOT disqualified.
In that particular challenge, the only immediate disqualifications were those with syntax errors or CURSOR solutions or those that attempted to create/use a UDF.
We're trying to make the rules clearer from here on out regarding the use of temp tables, etc.
Just by reading this challenge seemed to be slightly more complex than challenge 17, so I didn't try.
Well, you still have got a week to submit your solutions. So probably you should try!
This one is surprisingly easy!!!
So far, I have found #16 much interesting in terms of ideas.
can we use the following command as first line of the code:
set dateformat ymd
with out this my query is not giving right output for french language.
It will be helpful, if I get a confirmation on this.
I will have to change the code if this leads to disqualification
@Anand348:
The query should work regardless of the setting of SET DATEFORMAT or SET DATEFIRST.
So I'm afraid the answer is "No", you may not have a SET DATEFORMAT command as your first line of code.
The code should just be a single T-SQL statement (that begins with either WITH or SELECT).
Hello everybody!
just a small question :
is it possible to insert into @t twice the same month and year ? so did we have to take care of it ?
Best Regards
Matthieu
@Matthieu:
Good question!
No, there will not be duplicate month/year combinations in @t... you don't have to worry about that.
It would have been a good idea for a requirement, but there are probably too many solutions submitted already that didn't account for that.
So you can assume they are all unique.
Damn, I did code this way in case of the logic test ;)
You have suggested to use master..spt_values if necessary. But what if one run the query under non-privileged user account?
I still prefer to generate number table as we only need numbers between 1 and 31.
Will the performance difference due to use of master..spt_values and auxilary table matter the evaluation process?
Hi parth,
For this challenge, I would suggest you use master..spt_values. We will also use the same table for evaluation.
For the future, we are trying to publish the scripts for generating a 'standard' number table, which you can use in your solution. We will also use the same table for our evaluation.
Since you got so few solutions that passed the basic logic test, I'm submiting one.