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.

--Brad

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.

--Jeff Moden

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.

--Brad

Ok.. Thanks Brad.. I am going to submit my solution now...

@Ramireddy:

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.

--Jeff Moden

@Jeff:

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.

--Brad

Perfect.  I've submitted my code.  Thanks Brad.

--Jeff Moden

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.

--Brad

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

@Jeff:

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.

--Brad

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.

Brad,

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.

--Jeff Moden

@Brad,

Plamen is the one with the 8061 column width.  Someone should tell him, "Must look eye."  ;-)  

--Jeff Moden

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

--Brad

@Art:

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.

--Brad

@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!)

--Brad

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.

--Brad

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.

--Brad

Just by reading this challenge seemed to be slightly more complex than challenge 17, so I didn't 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).

--Brad

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.

--Brad

Damn, I did code this way in case of the logic test ;)

Hi Brad,

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?

Since you got so few solutions that passed the basic logic test, I'm submiting one.