This challenge presents a scheduleing problem related to recurring appointments. The task is to calculate and generate a list of appointments that are supposed to take place between two given dates. The calculation should be done based on the recurring configuration of each appointment.
Each appointment has a 'Schedule Start Date' and 'Schedule End Date' and any occurrences that fall outside those dates should be ignored. When generating the appointments between two dates, consider the start date and end date of each appointment.
Sample Data
Appointments Table
App# TaskName Begin Dur. Sch.Start Sch.End
---- ---------------- ----- ----- ---------------- ----------------
1 Sales Meeting 10:00 00:45 2010-01-01 11:00 2010-03-07 11:00
2 Project Meeting 10:45 01:30 2010-01-13 10:30 2010-03-11 14:00
3 Support Meeting 12:00 01:00 2010-01-11 08:00 NULL
4 Customer Meeting 11:25 03:00 2010-01-07 10:00 2010-02-23 21:00
5 Payroll Meeting 16:00 01:00 2009-07-23 10:00 NULL
6 Budget Meeting 10:00 08:00 2008-01-01 10:00 NULL
As mentioned earlier, while generating the schedule always consider the start date and end date of schedules. For example, the first schedule starts on 1st January 2010 11 AM and ends on 7th March 2010 11 AM. It indicates that the first possible occurrence of this schedule is 2nd January 2010. Similarly, If you query for any period later than 7th March 2010 11:01 AM, this schedule should not be listed.
Scheduling Table
The scheduling table stores the recurrence pattern of each appointment
App# Reccurrence Pattern Val
---- ----------- ------------------- ----
1 Daily Weekdays NULL
2 Daily Every N 2
3 Weekly Every N 1
4 Weekly Mon,Wed,Fri NULL
5 Monthly First Tue Every N 1
6 Yearly Third Tue Every Feb NULL
Pattern Reference
The 'Pattern' column in the Scheduling table defines the recurrence pattern. The pattern value should be interpreted with 'Recurrence' and 'Val' columns to identify the complete recurrence pattern of an appointment.
'Daily, Weekdays, NULL' indicates that the appointment should be repeated on all weekdays (monday to friday). 'Daily Every N 2' indicates that the appointment should be repeated every 2 days.
The following table helps to understand the various patterns supported.
Daily
--------------------------------------------
Weekdays - Repeat on all weekdays (Mon-Fri)
- VAL Column is ignored
Every N - Repeat every N days
- N is defined in the VAL column
Weekends - Repeat in all weekends (sat, Sun)
Weekly
----------------------------------------------
Every N - Repeat every N Weeks (once a week)
- N is defined in the VAL column
Mon - Repeat on Monday. VAL is ignored
Tue - Repeat on Tuesday. VAL is ignored
Wed - Repeat on Wednesday. VAL is ignored
Thu - Repeat on Thursday. VAL is ignored
Fri - Repeat on Friday. VAL is ignored
Sat - Repeat on Saturday. VAL is ignored
Sun - Repeat on Sunday. VAL is ignored
Monthly
-------------------------------------------------
Every N - Repeat every N Months (once a Month)
- N is defined in the VAL column
First Mon - Perform on the first Monday of the
Month.
- Supported identifiers: Mon, Tue, Wed,
Thu, Fri, Sat, Sun
Second #w - Perform on the Second #w (where #w
is one of the 7 days of week)
Third #w - Perform on the Third #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)
Yearly
--------------------------------------------------------
Every N - Repeat every N years (Once a year)
- N is defined in the VAL column
#Nth #w Every #m - "#Nth #w" behaves the same as Monthly
- For Example: "Second Monday"
- "Every #m" : #m is one of Jan, Feb,
Mar, Apr, May, Jun, Jul, Aug, Sep,
Oct, Nov, Dec
Based on the above information, here are a few more examples of possible data in the scheduling table.
Recurrence Pattern Val
---------- ------------------- ----
Daily Weekdays NULL
Daily Every N 3
Daily Weekends NULL
Weekly Every N 2
Weekly Mon,Wed,Fri NULL
Weekly Thu,Mon NULL
Monthly Every N 4
Monthly First Mon NULL
Monthly Last Tue NULL
Monthly Fourth Thu NULL
Monthly Third Tue Every N 2
Monthly Last Fri Every N 4
Yearly Every N 2
Yearly Last Thu Every Feb NULL
Yearly Third Sat Every Dec NULL
Expected Results
The challenge is to identify the appointments between the time period: 2010 Feb 20 10:00 and 2010 Feb 28 17:00. Here is the expected result based on the above data.
TaskName Date Start Duration
---------------- ---------- ----- --------
Project Meeting 2010-02-20 10:45 01:30
Sales Meeting 2010-02-22 10:00 00:45
Project Meeting 2010-02-22 10:45 01:30
Customer Meeting 2010-02-22 11:25 03:00
Support Meeting 2010-02-22 12:00 01:00
Sales Meeting 2010-02-23 10:00 00:45
Sales Meeting 2010-02-24 10:00 00:45
Project Meeting 2010-02-24 10:45 01:30
Sales Meeting 2010-02-25 10:00 00:45
Sales Meeting 2010-02-26 10:00 00:45
Project Meeting 2010-02-26 10:45 01:30
Project Meeting 2010-02-28 10:45 01:30
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.
Rules
- The solution should work on SQL Server 2005, 2008 or later versions.
- The solutions will be tested using SQL Server language setting "us_english".
- Sort the output by Date and Start Time.
- Schedule start date will always be less then 28th of the month.
- A 'case insensitive' collation will be used for testing the solutions.
Sample Script
Use the following script to generate the sample data.
-- Create the Appointment Table
IF OBJECT_ID('TC25_Appointments','U') IS NOT NULL BEGIN
DROP TABLE TC25_Appointments
END
GO
CREATE TABLE TC25_Appointments(
AppID INT IDENTITY PRIMARY KEY,
TaskName VARCHAR(100),
BeginAt CHAR(5),
Duration CHAR(5),
StartDate SMALLDATETIME,
EndDate SMALLDATETIME
)
GO
SET IDENTITY_INSERT TC25_Appointments ON
INSERT INTO TC25_Appointments (
AppID,
TaskName,
BeginAt,
Duration,
StartDate,
EndDate )
SELECT 1, 'Sales Meeting','10:00','00:45',
'2010-01-01 11:00','2010-03-07 11:00 ' UNION ALL
SELECT 2, 'Project Meeting','10:45','01:30',
'2010-01-13 10:30','2010-03-11 14:00' UNION ALL
SELECT 3, 'Support Meeting','12:00','01:00',
'2010-01-11 08:00',NULL UNION ALL
SELECT 4, 'Customer Meeting','11:25','03:00',
'2010-01-07 10:00','2010-02-23 21:00' UNION ALL
SELECT 5, 'Payroll Meeting','16:00','01:00',
'2009-07-23 10:00',NULL UNION ALL
SELECT 6, 'Budget Meeting','10:00','08:00',
'2008-01-01 10:00',NULL
SET IDENTITY_INSERT TC25_Appointments OFF
-- Create the Scheduling Table
IF OBJECT_ID('TC25_Schedules','U') IS NOT NULL BEGIN
DROP TABLE TC25_Schedules
END
GO
CREATE TABLE TC25_Schedules (
AppID INT NOT NULL FOREIGN KEY
REFERENCES TC25_Appointments(AppID),
Recurrence VARCHAR(50),
Pattern VARCHAR(50),
Val TINYINT
)
GO
INSERT INTO TC25_Schedules (
AppID, Recurrence, Pattern, Val
)
SELECT 1,'Daily','Weekdays',NULL UNION ALL
SELECT 2,'Daily','Every N',2 UNION ALL
SELECT 3,'Weekly','Every N',1 UNION ALL
SELECT 4,'Weekly','Mon,Wed,Fri',NULL UNION ALL
SELECT 5,'Monthly','First Tue Every N',1 UNION ALL
SELECT 6,'Yearly','Third Tue Every Feb',NULL
Restrictions
- The solution should be a single query that starts with a "SELECT" or “;WITH”
Notes
- Read the Submission Guidelines and make sure that your solution follows them.
- If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.