Is sunday is there in the weekdays??

sorry,  Rule5 answered my question

Thanks Jacob..

How is order of result determined?

Hi Jacob,

I guess you remove ID column from @x and @z tables of basic testing sandbox. I tried in the morning with solution without sorting order and it gives message of failure of solution. So I added sorting order in my solutions and it works and so I submitted there after.

My question is, does the sorting order in my solution breach the Rule, if so do I need to resubmit the solution or will you take care and remove the sorting order statement from my solution.

--Pragnesh

Pragnesh,

I just ran my solution in the sandbox and it works.

Note, that the Timing column is not just a simple concatenation between Schedule and Duration.

Hi Jacob,

Can you use UDF and a reference table?

Thanks,

Rob

Robert,

As per NOTE 1, your solution must start with 'SELECT' and cannot be preceeded by anything else.

And this obviously means that you cannot define and use @variables.

Can we assume that, ignoring the TrainingID primary key column, the combination of the 5 other columns (Training, ClassRoom, StartTime, Duration, Wk) in the source table is unique? In other words can we assume there are no duplicate schedules like the following:

Training    Classroom   Start Dur.  Wk

SQL Server  Silver-Room 10:00 02:00 M

SQL Server  Silver-Room 10:00 02:00 M

Can a class start in one day and end in the next day? For example, is it possible to have a class that starts at 23:00 with a duration of 01:30???

Thanks,

Nick

Is it possible to have the same Training in the same ClassRoom with overlapping hours? I see somebody asked about duplicated tuples, but I was wondering if there is any case like this:

Training    Classroom   Start    Dur.   Wk

=======  =========  ====    ====  ====

SQL Server  Silver-Room 10:00 02:00 M

SQL Server  Silver-Room 11:00 02:00 M

Thanks,

Nick

I'm grouping by both Start and Dur (in addition to training and classroom).  I am also filtering possible dupe Wk values.

This is one of the easier challenges because it is only a pivot issue (not hard in 2000 with single query), not a concatenation (hard in 2000 with single query) issue because you only need to concatenate the pivoted columns.

Can the StartTime or Duration be in the format '1:00' or they'll always be in the format '00:00'?

I was think at leading zeros for values lower the noon, ex. '9:00' instead of '09:00'.

Thanks!

What the max allowed duration? 23:59 or 24:00?

Does anybody knows how I can "emulate" SQL 2000 behaviour on my SQL 2005 (from Sql Server managment UI)?

I 'm not aware of syntax difference like "select top 1 1" afainst "select top(1) 1", so I need to face a strict syntax check (and I don't really feel like installing SQL server 2000).

BTW: I created a new DB with SQL 2000 compatibility level but I don't assume it'll be strict enough.

Sergejack,

No it's not strict enough. But if you stay away from the obvious like CTE, PIVOT and UNPIVOT you should be ok.

If you have any doubts about a specific syntax then just ask here.

I guess I'll have to wait and see the basic testing results.

Sergekack,

To you and all participants, this challenge may be simple. But remember that at the end the winner will be the one who not only gets the right results but who consumes the least amount of cpu under a stress test.

Earlier, Jesse mentioned that this problem reduces to a pivot and concatenation problem. This is indeed true and very neatly stated. But I can assure you that there is still room for different approaches where one approach consumes a lot less cpu than the other.

But PIVOT doesn't exist in 2k, right?

As far as I know, there no much ways to do "aggregate concatenation" with 2k, the best way seems to be using a variable (which would be against the challenge rules).

But I would love some brilliant mind to come with something unexpected ^^

Sergejack,

Unfortunately, PIVOT does not exist in 2K.

As you can see, I was on the Early Review team for this challenge. And when it was first proposed, I was amazed that I could not find a 'quick' solution. Finally, I came up with 'something' that solved the problem and it looked very strange. But it did the job.

Then I saw Jesse's comments on this page and I was amazed how his simple statement made me come up with something that appeared to be a lot more simple and straight forward.

Then I threw tons of data at both solutions and found that my 'strange' solution performed A LOT better.

In any case, I'll show you my solution when a winner for this challenge is declared.

I found this challange to be easier than I expeted. That probably means I did something really wrong :) If not, perhaps it is because I am forced to continue to work with SQL2k daily rather than getting to play with the shiny new toys 2K8 makes available :) Either way, it was nice to have a challange that didn't require 2K8 syntax, Thanks!!

How many CPU cores are used on the load testing?

I did a load test on mine via duplicating basic testing data 10,000 times

(MAXDOP 1)

SQL Server Execution Times:

  CPU time = 344 ms,  elapsed time = 663 ms.

(Parallelism permitted)

SQL Server Execution Times:

  CPU time = 436 ms,  elapsed time = 582 ms.

It could affect the placings since it factors in both CPU and Duration rankings - in which way probably would depend on the challenges and the submissions.

Cool, Then my 2nd version isn't needed.  It is faster than the first (sum of cpu+duration) with parallelism, but slower with MAXDOP 1.