This challenge presents a scheduleing problem related to recurring appointments..
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
Last #w - Perform on the Second #w (where #w
it should be like
Third #w - Perform on the Thrid #w (where #w
Fourth #w - Perform on the Fourth #w (where #w
Last #w - Perform on the Last #w (where #w
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
You can find the link to all the previous challenges at http://databasechallenges.com/
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.
@Robel,
You're right, it's a typo and can be safely ignored.
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.
Michael,
Yes, just move the DROP TABLE TC25_Schedules block to the very beginning.
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
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?
The output required is for a fixed period only: 20 Feb 10:00 to 28 Feb 17:00
20 Feb 10:00 to 28 Feb 17:00
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.
Jacob,
But for which year? Leszek_q shows that if a schedule end date is null, the results can be infinite.
Oh, i missed that. Yes you are right. it is only for year 2010.
sorry for the confusion.
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?
You can hard-code the input time periods. Variables are not allowed.
Can I use parts of date or number of days between dates?
@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.
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?
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.
Can you confirm this?
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'.
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
@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".
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.
dishdy is right. You can expect to have correct data as per the pattern reference.
Thanks for answers
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??
@Ramireddy
schedule start is Jan13th, not Feb13th
ooops....understood now...thanks..
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??
Yes, you should hard-code it in the solution.
Thanks Jacob, I submitted my solution..
But isn't hard-coding will limit the logic testing chances??
@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.
you can replace those dates exactly... :). But its additional step
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...
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.
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.
@jesse,
we count only the execution time.
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?
@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.
A simple question regarding 'Monthly'. Is
Monthly,First Mon,NULL
the same as
Monthly, First Mon Every N,1
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.
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?
By 25-Mar-2009 I meant 25-Mar-2010.
@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.
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?
In the previous post, I meant they don't involve the schedule start date as a starting point.
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.
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.
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
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' :-)
@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.
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."