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 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

This challenge is to Find discrepancies in the scheduled activities and actual activities performed by employees.


http://beyondrelational.com/puzzles/challenges/28/find-discrepancies-in-the-scheduled-activities-and-actual-activities-performed-by-employees.aspx

Share

Comments

# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 12:00 AM by Jacob Sebastian

Luts Mueller has compiled a tricky data set for this challenge. It is recommended that you take a look at it and test your solutions against that before submitting.

If any of you have built your own version of the tricky data that you test your solutions against, please feel free to share a link to it.

http://beyondrelational.com/blogs/ssctb/archive/2010/02/22/tsql-challenge-24-a-tricky-data-set.aspx


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 10:49 AM by andrijz

Sorry, probably I've missed from previous topics:

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 11:33 AM by Jacob Sebastian

Unfortunately, No.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 12:58 PM by Serg54

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?


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 1:18 PM by Serg54

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 1:20 PM by Serg54

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, February 22, 2010 4:29 PM by David S.

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 4:43 AM by Pragnesh Patel

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 8:46 AM by Jacob Sebastian

@Serge54,

Re: your question on FAQ 8 - The scenario you presented is acceptable. The restriction is not to have two appointments start and end within 30 minutes. In your example, only one appointment starts and ends within 30 minute and hence it is acceptable.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 8:48 AM by Jacob Sebastian

@Serge54,

Re: your question on FAQ12 - You are right. If the activity continues to the next slot, then it should be aligned to the end of the schedule, not to the start.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:05 AM by Ramireddy

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:10 AM by Sergejack

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:34 AM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:43 AM by Jacob Sebastian

@Ramireddy,

An activity cannot be scheduled more than once on a single day.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:45 AM by Jacob Sebastian

@Sergejack,

The Early Review Team came up with dozens of exceptional scenarios and to clarify them we added all those FAQ


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 9:48 AM by Pragnesh Patel

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 10:02 AM by Pragnesh Patel

@Imu92

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

-Pragnesh


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 11:25 AM by Sergejack

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 11:30 AM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, February 23, 2010 2:30 PM by Serg54

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, February 24, 2010 1:21 AM by Jacob Sebastian

@Serjack,

Every challenge has a certain level of complexity. Some times we alter the challenge to gain the desired level of complexity. The challenge rules are not expected to be 'real-life' rules. Some of them are added to make the task challenging. But we don't need to explicitly mention that. Complexity is part of every challenge coming out in this series.

Aligning an activity to the start or end time of a 30 minute slot solely depends on whether the activity is a continued from the previous slot or continues to the next slot. If the scheduled start time is between the start time and end time of a 30 minute slot, the actual start time should be aligned to it.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, February 24, 2010 1:22 AM by Jacob Sebastian

Hi all,

did we answer all the open questions in this thread? Let me know if any of the previous questions are left un-attended.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, February 24, 2010 3:35 AM by Sergejack

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, February 24, 2010 9:01 AM by Jacob Sebastian

@Sergejack,

I got you right. Added a new item to the NOTES. Please take a look.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, February 26, 2010 9:10 AM by Sergejack

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, February 26, 2010 12:15 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, February 26, 2010 12:32 PM by Jacob Sebastian

@Sergejack,

This is a rule we added after several discussion with the whole team working behind the challenges. So I would say, it has to stay :-)


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, February 26, 2010 12:43 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Sunday, February 28, 2010 12:07 PM by Anonymous

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Monday, March 01, 2010 4:06 AM by Sergejack

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'


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 2:08 AM by dishdy

@Sergejack:

T2 and T3 are overlapping appointments which is not allowed.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 3:14 AM by Sergejack

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 3:19 AM by Sergejack

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 11:14 AM by dishdy

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?


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 11:23 AM by Mike Lewis

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?


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 11:47 AM by dishdy

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 12:41 PM by Anonymous

@ 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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 2:18 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, March 02, 2010 11:47 PM by dishdy

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 12:33 AM by Jacob Sebastian

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 3:19 AM by Sergejack

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 5:15 AM by Jacob Sebastian

@SergeJack,

The rule 7 says "However, it is not possible to have two appointments that starts and ends within the span of 30 minutes.". It indicates that the maximum number of entries per 30 minute slot per employee is 3. One activity can start in the previous window and end in the current, a second activity can start and end within the current window and a third activity can start within the current window and end in the next.

Two activities cannot start and end within the same 30 minute window.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 9:48 AM by Sergejack

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 9:58 AM by Jacob Sebastian

@SergeJack,

Is it clear now?


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 11:52 AM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 3:46 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 8:48 PM by Jacob Sebastian

@lmu92,

There is no change in the rule. The rule on FAQ 7 is applicable to both appointment as well as the activity log tables.

Both test data sets you created are correct.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Wednesday, March 03, 2010 8:54 PM by Jacob Sebastian

@lutz,

The gap I am referring to is about breaking an activity into to pieces and have a gap between that. for example:

10:00 to 10:15 - activity1

10:20 to 10:30 - activity1

An activity cannot be performed like above. note that there is a gap between the same activity. An employee always perform the activities as a continuous block.

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 4:22 AM by Mike Lewis

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 8:02 AM by dishdy

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 8:26 AM by dishdy

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.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 11:14 AM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 11:50 AM by Mike Lewis

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 2:22 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 3:02 PM by dishdy

@Imu92:

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 3:21 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 3:35 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 4:20 PM by Anonymous

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Thursday, March 04, 2010 9:03 PM by Jacob Sebastian

@lmu92,

In this case, the start time of T2 should be considered as 10:45. The reason is that we need to align to the scheduled start time of an activity when ever possible.

One of my reply on 24th Feb says "If the scheduled start time is between the start time and end time of a 30 minute slot, the actual start time should be aligned to it."


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 12:19 AM by Ramireddy

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'


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 4:13 AM by Mike Lewis

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 4:19 AM by dishdy

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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 7:24 AM by Pragnesh Patel

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 7:57 AM by Pragnesh Patel

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 8:05 AM by Pragnesh Patel

@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


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Friday, March 05, 2010 10:26 AM by Jacob Sebastian

Pragnesh,

First of all, there is no physical order in the table. There is no clustered index or serial number. Records in the activity log table may appear in any order within the same time window.

As per FAQ 13, an activity cannot start prior to the scheduled time. So the record in activity log for T2 is invalid.


# re: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

Tuesday, June 22, 2010 3:03 AM by Jacob Sebastian

Hi All,

The tricky data for the logic testing is now available for review. beyondrelational.com/.../tricky-data-for-challenge-24.aspx


Copyright © Rivera Informatic Private Ltd.