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

@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

@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

@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

@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

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"

@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

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

@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

Hi Brad,

I have updated the permissions. I missed it when we moved the media libraries. sorry for that.

rgds

Jacob

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

@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

@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

Well, you still have got a week to submit your solutions. So probably you should try!

@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

@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

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.

rgds

Jacob