Facebook Sign in | Join

			5 MINUTES to source control your database
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

This is a challenge to Identify weekly schedule of training programs by course, classroom and timing.


http://beyondrelational.com/puzzles/challenges/30/identify-weekly-schedule-of-training-programs-by-course-classroom-and-timing.aspx

Share

Comments

# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 1:12 AM by Ramireddy

Is sunday is there in the weekdays??


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 1:31 AM by Jacob Sebastian

Note #6 says that the schedule can be only monday to friday. Not on Sunday.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 1:33 AM by Ramireddy

sorry,  Rule5 answered my question


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 1:37 AM by Ramireddy

Thanks Jacob..


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 4:20 AM by Leszek Gniadkowski

How is order of result determined?


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 5:24 AM by Jacob Sebastian

Leszek,

There is no output sorting requirement in this challenge.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 6:23 AM by Pragnesh Patel

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 6:36 AM by Jacob Sebastian

Pragnesh,

The sorting order in your solution does not breach the rule. What I said earlier is that there is no specific sorting requirement. As long as your query produces the required output (no matter in what order) your solution will pass the test.

rgds

Jacob


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 7:18 AM by dishdy

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 7:25 AM by Jacob Sebastian

I am glad to see that all of you are using the sandbox!


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 11:50 AM by RobertS

Hi Jacob,

Can you use UDF and a reference table?

Thanks,

Rob


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 1:36 PM by Jacob Sebastian

Robert,

No, UDF is not allowed. You cannot add your own reference tables. However if you wish to use a number/tally table, you can use the one specified in the 'notes' section of the challenge.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 2:10 PM by dishdy

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Monday, March 22, 2010 5:04 PM by andrewd.smith

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, March 23, 2010 12:35 AM by Jacob Sebastian

@Andrewd,

Yes you can make this assumption. There will not be duplicates.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, March 23, 2010 2:17 PM by licentiat

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, March 23, 2010 4:48 PM by licentiat

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, March 23, 2010 5:11 PM by Jesse Roberge

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Wednesday, March 24, 2010 1:43 AM by Jacob Sebastian

@licentiat,

1. A class cannot start one day and end on the next day.

2. The start time and duration of a given training and classroom combination will always be the same.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Wednesday, March 24, 2010 11:36 AM by licentiat

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!


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Wednesday, March 24, 2010 7:32 PM by cflemeta

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Wednesday, March 24, 2010 11:21 PM by Jacob Sebastian

@licentiat,

the duration will always be in hh:mm format.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Wednesday, March 24, 2010 11:22 PM by Jacob Sebastian

@cflemeta,

There is no specific rule about the maximum duration. However, out test data will not have more than 12 hours.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, March 25, 2010 6:34 AM by Sergejack

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, March 25, 2010 6:59 AM by Sergejack

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, March 25, 2010 7:41 AM by dishdy

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, March 25, 2010 8:34 AM by Sergejack

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, March 25, 2010 5:23 PM by dishdy

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Friday, March 26, 2010 4:22 AM by Sergejack

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 ^^


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Friday, March 26, 2010 3:36 PM by dishdy

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, March 30, 2010 8:30 PM by Bradley M. Small

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


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, April 08, 2010 7:58 PM by Jesse Roberge

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Thursday, April 08, 2010 9:31 PM by Jacob Sebastian

Load testing is done with MAXDOP 1


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Friday, April 09, 2010 2:02 PM by Jesse Roberge

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.


# re: TSQL Challenge 26 - Identify weekly schedule of training programs by course, classroom and timing

Tuesday, July 06, 2010 11:05 AM by Jacob Sebastian

we are about to begin the logic testing of this challenge. Does anyone like to volunteer to build the tricky data set?


Copyright © Rivera Informatic Private Ltd.