Sorry, probably I've missed from previous topics:

What about indeces? Do we have possibility to create indexes?

FAQ 8 :" However, it is not possible to have two appointments that starts and ends within the span of 30 minutes."

What about 9:00 to 10:05 Activity1, 10:05 to 10:25 Activity2, 10:25 to 10:55 Activity3 ?

I guess it's not possible too. I.e. no two appointments may start within the span of 30 minutes

whatever time they end. And no two appointments may end within the span of 30 minutes whatever time they start.

Is it correct?

FAQ 12 : "If an activity is scheduled to start at 10:00 for 20 minutes and if I see an entry for that activity in the log table at 10:00 for 20 minutes, how do I calculate the start time of the activity? The activity may have started at any time between 10:00 and 10:10.

In such a case, you should assume that the activity started on time (10:00)."

I think it may also depend on the next records as shown at the  Activity Log Table Example 1 ( modified a bit):

--

3.John    Training   2010-01-01 16:30:00 00:20' –- 16:10 to 17:00  

4.John    Training   2010-01-01 17:00:00 00:05' –- 17:00 to 17:05

5.John    HR Meeting 2010-01-01 17:00:00 00:10' –- 17:05 to 17:15

--

Activity 'Training' started at 16:40 and took 25 min instead of scheduled 20.

Or may be there should be a rule that  'Scheduled Duration' >= 'Actual Duration'.

Or I'm missing something :)

Sorry,

" However, it is not possible to have two appointments that starts and ends within the span of 30 minutes."

is really from FAQ 7.

Am I missing something, or is the duration in the extra test data for activities from Luts formatted wrong? Should:

SELECT 'John','HR Meeting','2010-01-02 09:30:00','05:00' UNION ALL

be

SELECT 'John','HR Meeting','2010-01-02 09:30:00','00:05' UNION ALL

Hi Jacob,

It seems the tricky data set compiled by Mueller has certain issues. The Duration column is having format mm:ss (Minutes:Seconds) instead it should have hh:mm (Hours:Minutes) format.

Please make sure, If i am wrong?

-Pragnesh

Is Scehdules table consists of  an Employee  having two records with same activity on same day???

eg:

John Training  1/1/2010  12:00AM

John Training  1/1/2010   5:00PM

in above 2 rows, John has Training Scheduled 2 times in a single day... Is this possible??

Did you add Faq 12 to add complexity to the problem?

Because that exception adds a lot of complexity.

We suddenly have to consider scheduled time in activity time calculation (which is not relevant for any human interpretation of the output).

Could it be that's what winning a SQL Prompt stands for? ^^

dschwant said:  

Am I missing something, or is the duration in the extra test data for activities from Luts formatted wrong? Should:

SELECT 'John','HR Meeting','2010-01-02 09:30:00','05:00' UNION ALL

be

SELECT 'John','HR Meeting','2010-01-02 09:30:00','00:05' UNION ALL

----------------------------------------------------Pragnesh Patel said:  

Hi Jacob,

It seems the tricky data set compiled by Mueller has certain issues. The Duration column is having format mm:ss (Minutes:Seconds) instead it should have hh:mm (Hours:Minutes) format.

Please make sure, If i am wrong?

-Pragnesh

-----------------------------------------------------

@dschwant

@Pragnesh

You are correct,

the data in the activity log are displayed in mm:ss but should be displayed as hh:mm.

I'll ask Jacob to correct it.

Sorry about the confusion!!!

@Imu92

Thanks for updates.

Another issue with Expected Results of tricky data set.

In 1st row, the ActSt showing 10:00. Instead it should be 10:10, As the activity start at '2010-01-02 10:00:00' and duration is '00:20'. So the activity duration is between 10:10 to 10:30

-Pragnesh

@Imu92

Sorry for my previous Post. I had not consider FAQ12 for above post.

-Pragnesh

@Jacob, with FAQ12 you clarify thing by introducing new complexity.

If you want to keep things clear and simple you could state that any activity begins at the lattest possible moment of the 30 minutes span it is bound to.

In such a case have activities that takes place on one or several 30 minutes span would bend to the same rule.

1. Begin time of any activity stick to the end of the 30m span.

2. End time of any activity stick to the begining of the 30m span.

If the complexity raise is wanted, pls state it inside the FAQ as well.

@Pragnesh (previous two posts):

That's what tricky test data are good for :-)

(if set up correctly, I have to admit...)

Reason why Jessicas Training1 activity lasts from 10:20 to 10:35:

There are entries in the activity log for 10:00 and 10:30. Therefore, the activity took place around 10:30.

Going back to the activity log it shows 10min before 10:30 and 5min after 10:30.

Since there are no gaps allowed within one meeting the result has to be 10:20 to 10:35.

Sergejack said:  

1. Begin time of any activity stick to the end of the 30m span.

2. End time of any activity stick to the begining of the 30m span.

What about "if begin/end time of an activity cann't be definitely infered from other log records, then begin time should be assumed to be as close to the scheduled begin time as possible". End time is always just begin time plus duration.

P.S. Feel like there still is some uncertanity in the challenge rules.

@Jacob

Don't get me wrong, I'm not against that complexity raise.

But I think that when all rules but one are to make the context simplier/clearer, the rule that make things more complicated should be marled in any appropriate way (with a red ! maybe).

Or you're quite bound to be asked "Is this really intentional?" or to see a ridiculously poor ammount of solutions to pass the logical testing.

We can have up to 3 different activities in a 30 minutes span which combined with rules 12 and 13 raise a lot of exception scenarios.

It doesn't look like any human readable queries could still be written out of that. Thus I don't believe I could have any fun with that.

@Sergejack:

As far as I can see it won't make the query that much more complicated (I had to add 5 lines to mine to make it work).

However, I think Jacob needs to make a decision whether we should consider such a scenario or not.

Whatever decision he'll come up with will be fine with me. ;-)

@Sergejack:

At a second thought: rules 12 and 13 actually make it easier rather than more complicated.

It will make it harder to build the load test data as well as the tricky test data (at least based on my experience...), since those data have to be verified to be compliant to the rules.

Once that's done, the query itself doesn't have to handle those exceptions.

The second set of tricky test data did not follow rule #13 but it was fairly easy to get it back into spec compliance. (@Jacob: would you verify it please?)

I set up a second test scenario.

It looks very simple but the tricky part is, that we end up with three entries for the 10:00:00 window in the activity log.

Here's the link:

[url]beyondrelational.com/.../another-tricky-scenario-to-test-your-solutions-for-tsql-challenge-24.aspx[/url]

The test scenario was published before but it failed rule #13 so Jacob took it offline. The issues have been resolved. Now it is (as to my best knowledge) compliant to all the rules.

Try this scenario :

INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)

SELECT 'A', 'T1', '2010-01-01 10:00:00', '00:30' UNION ALL

SELECT 'A', 'T2', '2010-01-01 10:30:00', '00:05' UNION ALL

SELECT 'A', 'T3', '2010-01-01 10:30:00', '00:10'

INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)

SELECT 'A', 'T1', '2010-01-01 10:00:00', '00:15' UNION ALL

SELECT 'A', 'T1', '2010-01-01 10:30:00', '00:15' UNION ALL

SELECT 'A', 'T2', '2010-01-01 10:30:00', '00:05' UNION ALL

SELECT 'A', 'T3', '2010-01-01 10:30:00', '00:10' UNION ALL

SELECT 'A', 'T3', '2010-01-01 11:00:00', '00:20'

@Sergejack:

T2 and T3 are overlapping appointments which is not allowed.

They do not overlap.

Here's the activy expected results :

T1 : 10:15:00 - 30 min

T2 : 10:45:00 - 5 min

T3 : 10:50:00 - 30 min

And if T2 were 4 min it would become

T1 : 10:15:00 - 30 min

T2 : 10:46:00 - 4 min (<= 10:46:00)

T3 : 10:50:00 - 30 min

I would love to have lmu92 opinion on how easy that really is.

You'r right, in appointment table there is an overlap, which is no big deal, here :

INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)

SELECT 'A', 'T1', '2010-01-01 10:00:00', '00:30' UNION ALL

SELECT 'A', 'T2', '2010-01-01 10:30:00', '00:05' UNION ALL

SELECT 'A', 'T3', '2010-01-01 10:35:00', '00:10' (<= 10:35)

It doesn't change anything about the expected result.

Sergejak:

Why do you say that if T2 were 4 min it would then start at 10:46? What is the exact reasoning behind that?

I'm still not 100% clear on FAQ 12 to be honest.  Let's say we have an activity scheduled to start way before it actually does, with no activities directly before or after.  If for example the activity log entry starts at 10am...

For a duration of 15m we assume a start time of 10:15.

For ANY other duration (e.g. 14m, 16m, 5m, 25m etc) we start at 10am.

Is this correct?

Michael Lewis,

Indeed the answer to FAQ 12 doesn't look right. It should start at 10:00 for all values of valid durations (1 to 30 minutes).

@ Sergejack:

Here's the activy expected results :

T1 : 10:15:00 - 30 min

T2 : 10:45:00 - 5 min

T3 : 10:50:00 - 30 min

And if T2 were 4 min it would become

T1 : 10:15:00 - 30 min

T2 : 10:46:00 - 4 min (<= 10:46:00)

T3 : 10:50:00 - 30 min

I would love to have lmu92 opinion on how easy that really is.

-----------------------------------

If you change T2 to 4 min then you'd need to either add one minute to T1 or T3 in the 10:30 window in order to meet rule #11 (no gaps). Based on that changed entries in the activity log it would either start on 10:46 (assuming T1 would last 1 min longer) or 10:45 (leading T3 to start one minute early).

If you provide sample data that will lead to gaps you break rule #11.

Lutz

@Michael:

--------------

I'm still not 100% clear on FAQ 12 to be honest.  Let's say we have an activity scheduled to start way before it actually does, with no activities directly before or after.  If for example the activity log entry starts at 10am...

For a duration of 15m we assume a start time of 10:15.

For ANY other duration (e.g. 14m, 16m, 5m, 25m etc) we start at 10am.

Is this correct?

---------------------

It depends. If you have an activity that started in the previous window" (e.g. John's Lunch activity that started in the 13:00 window but continued into the 13:30 window), the remaining portion of the activity will start at the beginning of the window to meet rule #11 (consecutive measures). Continue the Lunch example: since it started in the 13:00 window and lasts 45 minutes, there are 15 minutes left. So it will be from 13:30 to 13:45 in the 13:30 window. It doesn't matter how long the Lunch activity actually lasts (assuming >30min), the 13:30 section would always start at 13:30.

On the other side, if the activity will continue in the next window (e.g. John's HR Meeting starting in the 10:00 window  and ends in the 10:30 window), the first section of the activity needs to be measured from the end of the window backwards. If HR meeting has 15min in the 10:00 window, it will start at 10:15. If it had 10min in that window, it would start at 10:20 and 10:10 for 20min, respectively.

The tricky part is with three activites within a 30min window. That's only possible if one activity would start in the window before, the second one would begin and end within that window and the last one would continue into the next window (all three measures have to add up to 30min though...). In that case you'd need to either figure out how long the first one lasts within that window or when the last one started and claculate either the beginning or the end of the "middle" activity from that. For a sample please see my second tricky test data (link is a few posts back).

Ugghh! Long post. But I hope it helps to better understand the requirement.

Imu92,

FAQ 11 is a bit ambiguously written. But I think it refers to gaps within a single activity and not to a gap between one activity and another.

Thus T2 starts at 10:45 no matter whether its duration is 5 minutes or 4 minutes even if the latter creates a gap of 1 minute between T2 and T3.

@lmu92

What Jacob and dishdy just said are just what I think as well.

My example with 4 minute is valid.

@Jacob

Where is it written that there cannot be more than 3 activities within a 30 min span in the activity log? (I don't see that).

Say, when two task occurs later than expected, both in a single 30 min span, do we order them by putting the first scheduled first? And we do put them both at the end of the 30min span, right?

@Jacob

You should really be specific in the FAQ when you talk about an appointement as an activity that occured and when you talk about an appointement as a scheduled one.

For me rule 7 would be read like you were talking about the Appointement (see?) table entries.

@Jacob:

--------------------

"@dishdy is right. FAQ 11 intends to say that there cannot be a break within a single activity."

--------------------

If there are gaps allowed between activities and we use Sergejacks example from above, then it's impossible to say where you have that 1 min break (either before or after T2). So, both solution have to be possible or you'd need to clarify how to deal with that scenario.

--------------------

@dishdy:

"Thus T2 starts at 10:45 no matter whether its duration is 5 minutes or 4 minutes even if the latter creates a gap of 1 minute between T2 and T3."

--------------------

I can't see any rule that would confirm your statement. If T2 would start at 10:46, all rules are still followed. Either way would be correct.

Right now, it's just guessing. Clarification is required. Either expand the rules that ther will be no gaps between activities (in the log) if there are three activities within the same window or to come up with a rule where the break needs to be considered.

Jacob, please clarify.

@Jacob:

Your modification to the rule#7 is confusing to me.

It should refer to the activity table rather than the appointment table... otherwise we could end up with a schedule for one activity every 30 minutes (let's say 20 activities toal, from 6:00 until 16:00) but the activity log could show all those 20 activities within the same window at 16:30, lasting one minute each, without braking any rule as far as I can see. How could someone tell the order of those activities or the gaps in between with the rules given so far?

It would be totally different, if rule #7 would refer to the activity table though....

@Imu92,

Thanks for your effort in answering my query, however you unfortunately fell into the trap of having activities before and after.  What I want to know is how FAQ 12 works with no activities before and after to influence the start time.  i.e. why does it state that a 10:00am activity with a duration of 15m should be assumed to start at 10:15?  Can I see an example of how this FAQ works please?

I am comfortable with how activities before and after influence the start times, as well as when the activity in question begins before the time slot, or ends after it.  I really just need to know what the purpose of FAQ 12 is?

Thanks

Michael Lewis,

I think the second part of FAQ 12 is incorrect. If you have only a single entry in the activity log then you should make it start at the top of the half hour as close as possible the appointment start time (but not before it)

Also FAQ 13 is correct only if there is a second activity log record. If there is only one then it should start at 14:00 or appointment start time - whichever comes later.

Michael Lewis,

In reading your comment again and putting this together with FAQ13, you have the following interesting and tricky scenario (which, I just realized, my solution fails to handle)

You have an appointment scheduled at 09:15 for 00:15. You have a single activity log at 09:00 for 00:15. This must then start at 09:15 and NOT 09:00.

Do we all agree with that?

Now let's look at this scenario.

Schedule for me:

A1 08:45

A2 09:15

Activity log for me:

A1 08:30 00:30

A1 09:00 00:10

A2 09:00 00:05

This means A2 must start at 09:15 (and not 09:10).

But if activity log for me is:

A1 08:30 00:30

A1 09:00 00:20

A2 09:00 00:05

This means A2 must start at 09:20.

@ Jacob:

...

It is quite acceptable to have the following:

10:00 to 10:15 - Activity 1

10:20 to 10:30 - Activity 2

There is no change in the rules. This is what is intended in the rules where a gap between a single activity was mentioned.

------------------------------

How do you know that Activity 2 (assuming it will last for 10min) starts at 10:20 and not at 10:15,10:16,10:17, 10:18, or 10:19? What rule will acutally define the result you came up with?

@Imu92

I think Jacob's point was that you can have two activities in the same period with a gap between them (i.e. 5 minute gap between 10:15 and 10:20).  However you can't have a gap for a single activity,

e.g.

10:00 to 10:15 - Activity 1

10:20 to 10:30 - Activity 1

@Michael:

The point is well taken.

However, it opens the question where the gap will be, before and/or after. And I can't find any rule that will address this issue.

@Imu92:

Show me an example where the rules are ambiguous about where a gap between two activities should/could/should not/could not be.

@dishdy:

[quote]---------

A2 09:00 00:05 (scheduled)

A2 09:00 00:05 (log)

This means A2 must start at 09:20.

---------[/quote]

How do you know? It can start anywhere between 9:20 and 9:25. What rule is your decision based on?????

To make it even mor complicated, add

A2 09:15 00:03 (scheduled)

A3 09:20 00:03 (scheduled)

A2 09:00 00:01 (log)

A3 09:00 00:01 (log)

You wouldn't be able to tell  AT ALL when each activity actually took place nor would you even be able to tell which one started first! Two possible solutions: (A3 9:20 and A2 9:21) or (A2 9:27 and A3 9:29). You'll end up with dozens of possible combinations. Again my questions: What rule needs to be applied? What is the ONE AND ONLY correct solution and WHY?

Please ignore the "even more complicated" part from above. It isn't compliant with rule #7. But the other question still remain unanswered.

@dishdy:

[quote]

@Imu92:

Show me an example where the rules are ambiguous about where a gap between two activities should/could/should not/could not be.[(quote]

It's already in my post dated # March 4, 2010 8:14 PM

But here's another example based on one from several posts above:

schedule

T1 : 10:15 - 30 min

T2 : 10:45 - 5 min

T3 : 10:50 - 30 min

Activity log

T1 : 10:00 - 15 min

T1 : 10:30 - 15 min

T2 : 10:30 - 4 min

T3 : 10:30 - 10 min

T3 : 11:00 - 20 min

T2 can either start at 10:45 or 10:46. When does it start and why (please name the rule your answer refers to)?

Jacob,

What is the expected output in case of, all the activities are scheduled in the same time?

Suppose,

INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)

SELECT 'John','HR Meeting1','2010-01-02 07:00','00:05' UNION ALL

SELECT 'John','HR Meeting2','2010-01-02 08:05','00:05' UNION ALL

SELECT 'John','HR Meeting3','2010-01-02 09:10','00:05' UNION ALL

SELECT 'John','HR Meeting4','2010-01-02 10:15','00:05' UNION ALL

SELECT 'John','HR Meeting5','2010-01-02 10:30','00:05' UNION ALL

SELECT 'John','HR Meeting6','2010-01-02 10:45','00:05'

INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)

SELECT 'John','HR Meeting1','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting2','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting3','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting4','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting5','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting6','2010-01-02 11:00:00','00:05'

What is the expected o/p in above case? Is it like below??

INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)

SELECT 'John','HR Meeting1','2010-01-02 11:00:00','00:05' UNION ALL

SELECT 'John','HR Meeting2','2010-01-02 11:05:00','00:05' UNION ALL

SELECT 'John','HR Meeting3','2010-01-02 11:10:00','00:05' UNION ALL

SELECT 'John','HR Meeting4','2010-01-02 11:15:00','00:05' UNION ALL

SELECT 'John','HR Meeting5','2010-01-02 11:20:00','00:05' UNION ALL

SELECT 'John','HR Meeting6','2010-01-02 11:25:00','00:05'

@Ramireddy,

That situation would never happen, as the rules state that you can't have more than one activity starting and ending within the same 30-minute slot.

@Ramireddy,

Your first activity log set violates FAQ 7. Your solution will not be exposed to this kind of data in the tricky data test. Even though it refers to the appointments table, I think this applies to the activity log as well.

You second activity log set violates FAQ 3 which says time stamp can only be xx:00 or xx:30.

@Jacob,

In continuing to @lmu92 Question, Lets tweak the data

schedule

T1 : 10:15 - 30 min

T3 : 10:45 - 30 min

T2 : 11:15 - 5 min

Activity log

T1 : 10:00 - 15 min

T1 : 10:30 - 15 min

T2 : 10:30 - 4 min

T3 : 10:30 - 10 min

T3 : 11:00 - 20 min

In this scenario, what should be the start time for T2?

-Pragnesh

@Jacob,

Can it be assume the records in the activity log table is stored as per the activity actually happens. I attract to assume this because of the LogID field is Auto incremental.

If so, in that case I think the complexity of the challenge will drastically reduce :)

-Pragnesh

@Jacob,

Sorry for wrong data in previous post in continuing to @lmu92 Question

Consider this one-

schedule

T1 : 10:15 - 30 min

T3 : 10:45 - 10 min

T2 : 11:15 - 5 min

Activity log

T1 : 10:00 - 15 min

T1 : 10:30 - 15 min

T2 : 10:30 - 4 min

T3 : 10:30 - 10 min

In this scenario, what should be the start time for T2?

-Pragnesh