TSQL Challenge 25 - Appointments and Recurring Schedules
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.
Appointments Table
Here is the contents of the 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
The Challenge
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.
Expected Output
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.
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
Sample Data Scripts
Use the following sample scripts to generate the sample data for this challenge.
-- 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
Notes
- The solution should be a single query that starts with a "SELECT", ";WITH" or "WITH"
- Read the Submission Guidelines and make sure that your solution follows them.
- The solution should work on SQL Server 2005, 2008 or later versions
- If you wish to use a tally-table/number-table, you can use the one provided here . Do not include the scripts to generate this table in your solution. You can assume that this table will be present on the database where we run the solutions during the evaluation process.
- Sort the output by Date and Start Time
- Use this forum for any questions related to TSQL Challenge #25
- The solutions will be tested using SQL Server language setting "us_english"
- Schedule start date will always be less then 28th of the month. Thanks dishdy for identifying the scenarios that could create problems in the absense of this rule.
- A 'case insensitive' collation will be used for testing the solutions
- Last Date to submit your entries: 22 March 2010 Midnight GMT.
Thanks to the Early Review team who reviewed this challenge and suggested corrections and enhancements to make it better. Special thanks to dishdy, dave ballantyne and Lutz.
Challenge Evaluation Details
Though the evaluation of this TSQL Challenge is completed and winners are announced, you can still submit a solution and we will be very happy to review it. Before submitting, make sure that your solution passes the basic testing and logic testing and the performance statics are good. See the submission guidelines before submitting your solution.