Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
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 25 - Appointments and Recurring Schedules

This challenge presents a scheduleing problem related to recurring appointments..


http://beyondrelational.com/puzzles/challenges/29/appointments-and-recurring-schedules.aspx

Share

Comments

# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Sunday, March 07, 2010 11:34 PM by Ramireddy

Small mistake in below 3 sentences. For every week, explanation is given as same second week,

Third #w  - Perform on the Second #w (where #w        

is one of the 7 days of week)            

Fourth #w - Perform on the Second #w (where #w

is one of the 7 days of week)            

Last #w   - Perform on the Second #w (where #w        

is one of the 7 days of week)  

it should be like

Third #w  - Perform on the Thrid #w (where #w        

is one of the 7 days of week)            

Fourth #w - Perform on the Fourth #w (where #w

is one of the 7 days of week)            

Last #w   - Perform on the Last #w (where #w        

is one of the 7 days of week)  


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 08, 2010 5:05 AM by OldTechGuy

Hi Just joined, I was intrigued by the current challenge. As a newbie to this site I thought I'd look at other challenges to see the range and possibly pick up cool ideas from the solutions. I've tried to access a list of challenges, but cannot find one on the site. Would it be possible to have a link to old and current challenges: with a description of the challenge under the number and where appropriate a link to the winning solution? Thanks I'm always looking to improve the performance of our staff bank agency software http://www.ava.co.uk  


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 08, 2010 5:10 AM by Jacob Sebastian

You can find the link to all the previous challenges at http://databasechallenges.com/


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 08, 2010 3:07 PM by Robel

Hi, not a big deal but I noticed that the sample data scripts set Identity Insert on TC25_Appointments to ON before inserting into TC25_Appointments and also afte the insert statment. Is that intentional? I am thinking its just a typo and the second one should actually set the identity insert to off.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 08, 2010 4:59 PM by dishdy

@Robel,

You're right, it's a typo and can be safely ignored.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, March 09, 2010 4:08 AM by Mike Lewis

Also not a huge point, but the sample data needs to be reordered to make it re-runnable.  The foreign key constraint stops the appointments table being dropped first.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, March 09, 2010 8:20 AM by dishdy

Michael,

Yes, just move the DROP TABLE TC25_Schedules block to the very beginning.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 6:14 AM by Leszek Gniadkowski

If input data is like

App# TaskName         Begin Dur.  Sch.Start        Sch.End          

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

1    Sales Meeting    10:00 00:45 2010-01-01 11:00 NULL

App# Reccurrence Pattern             Val

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

1    Daily       Weekends            NULL

expected output is

TaskName         Date       Start Duration

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

Sales Meeting    2010-02-20 10:00 00:45  

Sales Meeting    2010-02-21 10:00 00:45  

Sales Meeting    2010-02-27 10:00 00:45  

Sales Meeting    2010-02-28 10:00 00:45  

or

TaskName         Date       Start Duration

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

Sales Meeting    2010-02-20 10:00 00:45  

Sales Meeting    2010-02-21 10:00 00:45  

Sales Meeting    2010-02-27 10:00 00:45  

Sales Meeting    2010-02-28 10:00 00:45  

Sales Meeting    2011-02-20 10:00 00:45  

Sales Meeting    2011-02-26 10:00 00:45  

Sales Meeting    2011-02-27 10:00 00:45  

Sales Meeting    2012-02-25 10:00 00:45  

Sales Meeting    2012-02-26 10:00 00:45  

etc.

Output is limited only by the 2010 year or not?

If not, how should be limited output in this example, by max year or max numbers of rows?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 6:57 AM by Jacob Sebastian

The output required is for a fixed period only: 20 Feb 10:00 to 28 Feb 17:00


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 7:17 AM by Leszek Gniadkowski

20 Feb 10:00 to 28 Feb 17:00

or

20 Feb 2010 10:00 to 28 Feb 2010 17:00?

I think that 20 Feb 2010 10:00 to 28 Feb 2010 17:00,

because of excepted output of App# 3 limited to 2010 year.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 7:18 AM by dishdy

Jacob,

But for which year? Leszek_q shows that if a schedule end date is null, the results can be infinite.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 8:27 AM by Jacob Sebastian

Oh, i missed that. Yes you are right. it is only for year 2010.

sorry for the confusion.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 10:40 AM by Leszek Gniadkowski

Which way can we use to input time period? Cte, table variable, any type (string, int, datetime), parts of dates, hard coded dates or parts (and reusing). Is any method allowed? Which method is preferred?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 10:55 AM by Jacob Sebastian

You can hard-code the input time periods. Variables are not allowed.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 11:17 AM by Leszek Gniadkowski

Can I use parts of date or number of days between dates?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 11:41 AM by dishdy

@leszek_g,

You are free to hard code those dates any way you like. You can even hard code information derived from those two dates.

If you use a CTE approach you might want throw these two dates into a table with a single row and possibly any other information you find useful.

This way you have to do the hard-coding only once.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 10, 2010 5:50 PM by Leonid Koyfman

I think we need a better definition of "Every N."

What point in time should we start to count from?

Is it Sch.Start for the TaskName?        


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Thursday, March 11, 2010 3:18 AM by dishdy

Leonid,

I was under the impression that you make the first possible placement in the 20-feb-10 to 28-feb-10 interval and then count from there. But looking at App#3 it appears you count from the task's Sch.Start date.

Jacob,

Can you confirm this?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Thursday, March 11, 2010 4:06 AM by Jacob Sebastian

For 'Every N' recurring pattern, you always start from the start date of the schedule. So the first occurrence will be on the schedule start date and then it will repeat 'Every N'.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Thursday, March 11, 2010 12:19 PM by Leszek Gniadkowski

Can be for example data in the Scheduling table like these rows?

Combined patterns:

Daily 'Weekends Every N' 3

Weekly 'Mon,Wed,Fri Every N' 2

Yearly 'Last Thu Every Feb Every N',2

Not null Val without 'Every N' in Pattern:

Daily 'Weekends' 1

Pattern 'Every N' not at end:

Monthly 'Every N Third Tue' 2


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 12, 2010 3:48 AM by Jacob Sebastian

@leszek,

"Every N" cannot come with weekends or weekdays. Weekly cannot have a weekday and Every N in the same pattern. Either it will be 'weekly every N' where you need to repeat the schedule every N week from the start date or it will be a list of one or more weekdays.

All the possible combination of patterns are given in the table under section "Pattern reference".


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 12, 2010 4:53 AM by dishdy

@leszek_g,

I think it's safe to assume that there will be no invalid data in the schedules table. You should expect only data of the type shown in the list of examples.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 12, 2010 5:19 AM by Jacob Sebastian

dishdy is right. You can expect to have correct data as per the pattern reference.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 12, 2010 7:57 AM by Leszek Gniadkowski

Thanks for answers


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Saturday, March 13, 2010 2:44 AM by Ramireddy

I have a doubt with the output.

Project meeting sch. start is Feb13th 10:30 and begin time 10:45.

That means, first time project meeting happened at Feb13th, 10:45  

and the meeting is scheduled for every 2 days, so , next meetings will be at Feb 15,Feb 17,Feb 19,Feb 21

But in the output expected, project meeting is shown as Feb 20.

what is the reason behind this??


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Saturday, March 13, 2010 5:05 AM by Leszek Gniadkowski

@Ramireddy

schedule start is Jan13th, not Feb13th


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Sunday, March 14, 2010 6:40 AM by Ramireddy

ooops....understood now...thanks..


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 15, 2010 10:55 AM by Ramireddy

Jacob,

  The output needs to list the schedules between the dates feb20 and feb28. Is it means, are we hard-coding these 2 dates in our solution??


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 15, 2010 11:54 AM by Jacob Sebastian

Yes, you should hard-code it in the solution.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 15, 2010 1:45 PM by Ramireddy

Thanks Jacob, I  submitted my solution..

But isn't hard-coding will limit the logic testing chances??


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Monday, March 15, 2010 4:13 PM by dishdy

@Ramireddy,

In the beginning I thought that hard-coding the start/end date-time period would limit the logic testing.

But if you think about it a little, you will realize that this represents no limitations.

Their logic testing data will test your code completely even with those hard-coded start/end date-time values.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, March 16, 2010 12:50 AM by Ramireddy

you can replace those dates exactly... :). But its additional step


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, March 16, 2010 4:43 AM by Mike Lewis

Can I make the assumption that the challenge will always stick to 20th-28th Feb?  i.e. you will not change the hard coded dates in the solutions as part of the testing?

There are certain patterns that can never happen between those dates, so I'd rather not have to code for them...


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, March 16, 2010 5:21 AM by Jacob Sebastian

@Ramireddy,

We will not replace the date values you write in your solution. For the logic testing, we will create tricky schedules so that we can still test them within the given time period.

@Michael,

The challenge will always stick to 20th to 28th Feb.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Wednesday, March 17, 2010 8:14 PM by Jesse Roberge

Do you count parse and compile time in the load testing?  The CPU time and elapsed time on the parse  & compile is over 8-10 times the cpu time than the query itself (for the basic testing dataset).

SQL Server parse and compile time:

  CPU time = 146 ms, elapsed time = 146 ms.

(12 row(s) affected)

Table 'Worktable'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TC25_Appointments'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TC25_Schedules'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 16 ms,  elapsed time = 18 ms.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Thursday, March 18, 2010 2:00 AM by Jacob Sebastian

@jesse,

we count only the execution time.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 7:41 AM by karinloos

I have a question pertaining to the definition of 1st week and in particular the 1st week of Jan ( in lieu of tricky data)

Do we apply  the ISO standard  or go with the default SQL week numbering?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 7:50 AM by Jacob Sebastian

@karinloos,

There wont be recurrence patterns such as "1st week of Jan". But what we have is "First friday of Jan" or "Second tuesday of Feb" etc. In such case, rather than looking at the week number, you should look into the occurrence of that weekday in the month.

"Second Tuesday of Feb" is 9th Feb. Tuesdays in Feb are 2, 9, 16, 23. So the ISO Week number or SQL Week number are not relevant in identifying the weekly recurrence patterns.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 9:10 AM by dishdy

A simple question regarding 'Monthly'. Is

Monthly,First Mon,NULL

the same as

Monthly, First Mon Every N,1


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 10:44 AM by Jacob Sebastian

Yes, You can assume 'Monthly first Mon' translates to be 'First Monday every month'.

In our testing we will make sure that we always use  "Monthly, First Mon Every N,1" syntax for this type of cases.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 11:52 AM by dishdy

One additional clarification regarding the 'Monthly' recurrences. Whereas 'Monthly,Every N,k' clearly is with respect to the appointments' schedule start date, this appears to be true also true for all the other 'Monthly' recurrences.

Here are some examples using the challenge's 20-Feb-2010 to 28-Feb-2010 period.

Appointment scheduled start date 20-Nov-2009 and 'Monthly,Last Wed Every N,3'. I proceed to find the first 'Last Wed' after the schedule start date which is 25-Nov-2009 and start counting. From here I have to 'move 3 months onwards' to February and find 'Last Wed' which happens to be 24-Feb-2010. Thus this will be in the output.

Appointment scheduled start date 26-Nov-2009 with same recurrence. I proceed to find the first 'Last Wed' which is 29-Dec-2009 and start counting. From here I have to 'move 3 months onwards' to March and find 'Last Wed' which now falls on 25-Mar-2009 and will thus NOT be in the output.

Do agree with this interpretation?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 11:58 AM by dishdy

By 25-Mar-2009 I meant 25-Mar-2010.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 12:11 PM by Jacob Sebastian

@dishdy,

In the second example, the first occurrence should be in November (which falls before the schedule start date - and hence will never be listed) . But EVERY N should start counting from November.

To summarize, for 'Monthly Every N' consider the month in which schedule starts as starting point. For 'yearly every n' consider the year in which the schedule falls as the starting point.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 12:37 PM by dishdy

For plain vanilla 'Every N' there is never any doubt about the first 'occurence': it is the scheduled start date or the next day (if begin time is before time of shedule start datetime).

Just to be sure we are on the same wavelength, certain recurrence/patterns are generically calendar based and do not involve the appointment schedule start date at all. These are 'Daily,Weekdays,NULL', 'Daily,Weekends,NULL', 'Weekly,week-day-list,NULL','Yearly,week-day-spec,Every month-name'.

Do you agree with that?


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, March 19, 2010 12:43 PM by dishdy

In the previous post, I meant they don't involve the schedule start date as a starting point.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Saturday, March 20, 2010 6:46 AM by Jacob Sebastian

@dishdy,

you are right. The recurrences of schedules such as 'daily weekdays null', 'daily weekends null' etc can be calculated without looking into the scheduled start date.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, April 13, 2010 1:52 AM by karinloos

I am a bit confused regarding the logic testing data in terms of comments made above regarding assumptions that could me made from the test data.

1.   As per Dishdy  on March 12  " think it's safe to assume that there will be no invalid data in the schedules table." , however when looking at the test data for logic testing I notice that for appid 33 and 34 the BeginAt times do not conform to this. (  I would have expected 08:45  and not 8:45. for example )

2.  As per Jacob on March 19  "Yes, You can assume 'Monthly first Mon' translates to be 'First Monday every month'.  In our testing we will make sure that we always use  "Monthly, First Mon Every N,1" syntax for this type of cases."   Yet I see many cases where the pattern/Recurrence is Monthly, First Mon,  50 etc. (ie without the Every N  but val filled in )    or   Monthly', 'Last Wed', NULL   which is a direct contradiction to the statement by Jacob.

Could you please clarify the above.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Tuesday, April 13, 2010 2:05 AM by Jacob Sebastian

@karinloos,

The logic testing data that you see currently is under draft mode and we have not started the testing. The idea is to make the data available and collect some feedback so that we can spot if there are any problems with the data.

It looks like the current data needs some changes and we will once again validate it against all the rules before proceeding with the testing.

One of the most difficult part of the evaluation process is creating the logic testing data. If any of you wants to come forward and help us doing this, you are welcome.

rgds

Jacob


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, April 16, 2010 1:26 PM by dishdy

I see you fixed the 'Every N Every N' pattern.

As for

23 Meeting about Drinking Beer 00:00 03:00 2010-02-21 00:00:00 2010-02-25 00:00:00

23 Monthly Fourth Thu Every N 1

I disagree with your testing result of

Meeting about Drinking Beer 2010-02-25 00:00 03:00 Missing

It violates the basic principles of date/time intervals and the concept of when something ends and when something begins.

Mind you, I have nothing against 'Drinking Beer' or having a 'Meeting about Drinking Beer' :-)


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Saturday, April 17, 2010 4:20 AM by Jacob Sebastian

@Dishdy,

You are right. Seth is working on updating the sandbox. Once the new version of sandbox is available, I will let you know and will re-do the logic testing.


# re: TSQL Challenge 25 - Appointments and Recurring Schedules

Friday, April 23, 2010 5:27 AM by Jacob Sebastian

Seth has been working on reviewing the data and I just heard back from him. It looks like the data is correct and no changes needed. Here are the comments from Seth:

"The mentioned appointment "Meeting about Drinking Beer" doesn't occur at 3:00, it lasts 3 hours.  It actually occurs at 00:00 which is allowable based on the rules for schedules.  Specifically:  

"Note that the duration is not affected by the schedule end date. If the end date of a schedule is '10-10-2010 11:00 AM' and the appointment is configured to start at 10:00 AM for 3 hours, it can occur on 10-10-2010 at 10:00 AM and can last for 3 hours. It can still occur on the same date if the schedule end date is '10:00 AM'. However, it cannot occur on that date if the schedule end date is '09:59 AM' or lesser."

The fourth thursday of the month is 2/25.  The schedule ends 2/25.  The appoinment is at 00:00 which is the exact moment of it would occur and the schedule ends... so the appoinment occurs.  No re-testing necessary."


Copyright © Rivera Informatic Private Ltd.