TSQL Challenges

TSQL Challenge 20 – Evaluation Started

We have started the evaluation of TSQL Challenge 20. The list of submissions we received so far are listed at http://databasechallenges.com/SQLServer/TSQL/challenge20/submissions. So far we received 170 submissions and I think this is one of the challenges with the highest number of submissions. If you have submitted a solution and don’t see it in the above list, please let us know.

We have scheduled the Load Testing of TSQL Challenge 17 and Logic Testing of TSQL Challenge 18 over this weekend. We will process all the solutions from the media library up to Friday for the evaluation of this weekend.

Claim your SQL Stars!

In my previous post I mentioned that we have setup a new home page for TSQL Challenges. The URL is http://beyondrelational.com/tc.aspx. We have been working on displaying the names of people with most SQL Stars on the page and I am very happy to announce that we are just done with it. Go to http://beyondrelational.com/tc.aspx and you will see the list of people with most SQL Stars.

Is this list complete?

No. What you see there is the results of challenges 13 to 16. We started using the new challenge platform from challenge 13. Our goal currently is to catch up with the pending evaluation queue, which we will do in 3 to 4 weeks. After that, we will go back to challenges  1 to 12 and load the results, winners and SQL Stars into the new platform. Once this is done, you will get to see the complete statistics.

Claim your SQL Stars

As all of you know, we have a very humble beginning and then we improved in terms of quality and process over period of time with the inputs and participation from all of you. When we started we accepted submissions by email and later on we decided to use the media library. There are a few people who submitted solutions and gained some SQL Stars but we are unable to link them to their user accounts. Here is the list of those people:

1. Adam Machanic
2. Adan Bucio
3. Adan Bucio
4. Jesse Mclain
5. Jesse Reich
6. Kevin Suchlicki
7. Mark Cowne
8. Mike Defehr
9. Randy Derby

All the above people gained some SQL Stars and currently we are not able to associate those stars with their user account. It could be that they have a different username that we are not able to relate directly. If your name is in the list, please let us know and we will assign the points to your user account.

How is SQL Stars awarded?

The top 10 winners of each challenge will get SQL Stars. They get 1 to 10 stars depending upon the rank of their solution. The solution coming on rank 1 will get 10 stars, the next will get 9 and so on. If more than one submission from the same user comes in the winning list, we will take the one submission that has the highest rank.

Reevaluation

There are times when we decide to reevaluate a challenge. In such a case we will perform a reevaluation with different sets of testing parameters. However, we will make sure that we keep the original challenge rules. One reason for reevaluation could be when we identify a mistake that we did in the previous evaluation and want to correct it. Another case could be when we get a few new submissions that are better than the existing winning solutions. After a reevaluation the results will be refreshed and the SQL Stars will be recalculated.

I am looking forward to hear your comments on this.

New Home Page for TSQL Challenges

One of the complaints we often received from people is about locating the previous challenges. Though http://databasechallenges.com lists all the challenges and the results, many people are not aware of it. We created a new page at beyondrelational.com which provides you easy access to the previous challenges. Take a look at the page at http://beyondrelational.com/tc.aspx

We will add more relevant stuff to this page soon. We are in the process of adding a ‘top’ list which displays the top winners, people with highest number of challenge participations etc. We look forward to hear your comments, suggestions and feedback on this page. On the forum area, I wanted to list the forum posts associated with TSQL challenges only. It currently displays all the forum threads, but we will limit it to only TSQL challenge threads (once we figure out how to do that :-)

If any of you are good in customizing community server, you might be able to help me to customize this URL a bit. I am struggling with something for last few days and am looking for help. If you are good in customizing community server and would like to volunteer to help, please contact me privately.

Hope all of you will like the new page.

Tricky data for TSQL Challenge 18 – English, Swedish and Czech

We are getting ready for the logic testing of TSQL Challenge 18. The tricky data for the logic testing is published at http://databasechallenges.com/SQLServer/TSQL/Challenge18/logic_testing. We will test the solutions with SQL Server language settings set to English, Swedish and Czech as explained in the tricky data listing.

We will perform the tests over the weekend and will announce the results of logic testing early next week. You can expect to see the basic testing results of challenges 19, 20 and 21 this week.

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.

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.

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

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

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 

1. The solution should be a single query that starts with a "SELECT", ";WITH" or "WITH"
2. Read the Submission Guidelines and make sure that your solution follows them.
3. The solution should work on SQL Server 2005, 2008 or later versions
4. 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.
5. Sort the output by Date and Start Time
6. Use this forum for any questions related to TSQL Challenge #25
7. The solutions will be tested using SQL Server language setting "us_english"
8. 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.
9. A 'case insensitive' collation will be used for testing the solutions
10. 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.

TSQL Challenge 18 – Basic Testing Results

We just published the basic testing results of TSQL Challenge 18. You can find them on http://databasechallenges.com/SQLServer/TSQL/Challenge18/basic_testing_results.

Almost half of the solutions failed to produce the EXACT output specified in the challenge description. If you see that your solution did not pass the basic testing, we would suggest you review the results of your solution and make sure that it matches 100% with the output given in the challenge. If you still could not spot the problem with your solution, contact us and we will let you  know what went wrong with your solution.

We are currently integrating the notification module to the evaluation process, so that you will get a notification at each evaluation phase. If your solution fails, the notification will give you the exact reason for the failure. Till the notification is fully integrated, you can contact us and we will do it manually.

TSQL Challenge 19 – List of accepted solutions

We have reviewed all the solutions received so far and the list of accepted solutions is posted at http://databasechallenges.com/SQLServer/TSQL/challenge19/accepted. If you don’t see your submission in the list please let us know.

We integrated an email notification feature to the evaluation process. Many of you must have received an email with the status of your submission. We are working on integrating similar notifications to each step in the evaluation process so that you can track the progress of your solutions.If you have received notifications for one or more of your submissions, please share your comments and feedback with us.

TSQL Challenge 19 – List of submissions received

We have started the evaluation of TSQL Challenge 19 and here is the list of submission we received till 26 Feb 2010. You can find the list of all the solutions at http://databasechallenges.com/sqlserver/tsql/challenge19/submissions. Solutions submitted to the media gallery after 26 Feb 2010 will be processed over the weekend and the above listing will be updated.

We are trying to complete the evaluation of challenge 17, 18 and 19 by the end of next week. One of the areas some of you can help us with this process is by helping us to build the tricky-data needed for testing these solutions. If any of you would like to volunteer and build the data for the logic testing of these solutions, send me a private note at jacob@beyondrelational.com.

TSQL Challenge 18 – Accepted solutions

We have reviewed all the solutions we received for TSQL Challenge 18. A list of solutions accepted and queued for further processing is published at http://databasechallenges.com/SQLServer/TSQL/Challenge18/accepted. This includes all the solutions we received till 26th February. If you have submitted a solution after that, it will be processed over this weekend and this list will be updated by early next week.

If your solution is rejected due to some reason, you must have received an email with the reason for rejection. In such a case, you can make the required corrections and resubmit your solution.

TSQL Challenge #18 – Evaluation Started

We have started the evaluation process of TSQL Challenge 18. You can see a list of solutions that we have received so far at http://databasechallenges.com/SQLServer/TSQL/challenge18/submissions

Note that the above list includes all solutions we received till last Friday. If you have submitted a solution after Friday, you will see it in the list early next week.

If you have submitted a solution earlier than last friday and don’t see it in the list, please let us know.

Looking at the progress we are making with the evaluation process, it looks like we will be able to clear all the backlog by early April.

Logic Testing Data for TSQL Challenge 17

We are about to start the logic testing of TSQL Challenge 17. The tricky data for this challenge is published at http://databasechallenges.com/SQLServer/TSQL/Challenge17/logic_testing.

We would like to request everyone to take a look at the logic testing data and the expected results and see if you find them to be fair enough.

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

This is a problem that I had seen in one of the forums many years back. This problem is related to a reporting requirement from the data stored by a 'poorly designed' system. The system has one table that stores the appointments scheduled for each employee. There is another table that keeps tracks of the activities performed by employees.

Red Gate SQL Prompt, a SSMS plug-in that increases how fast you work with SQL, is sponsoring this week’s TSQL challenge 24. The top winner of TSQL Challenge 24 will win a license of SQL Prompt Pro.

Your task is to generate a report that links the scheduled tasks and activities performed in the given output format.

The system uses a schedule table to keep track of the activities scheduled by each employee. Here is how this table looks like.

EmpName Activity   StartTime           Duration
------- ---------- ------------------- --------
John    HR Meeting 2010-01-01 10:15:00 01:30
John    Lunch      2010-01-01 13:00:00 00:45
John    Training   2010-01-01 15:00:00 01:00
Mike    HR Meeting 2010-01-01 10:15:00 01:30
Mike    Lunch      2010-01-01 13:00:00 00:45
Jessica Training   2010-01-01 11:20:00 00:30

The table does not have a primary key. The name of the employee is used as the key across the system. Employee names are guaranteed to be unique in this system. Here is how the first record is interpreted: "John is supposed to attend the 'HR Meeting' at 10:15 on 1st January 2010. The meeting will be 90 minutes long".

Another system keeps track of the actual activities of the employees against the scheduled activities. This system writes the activities to an 'Acitvity Log' table. This system logs the activies every 30 minutes. This is how the data in this table looks like:

EmpName Activity   StartTime           Duration
------- ---------- ------------------- --------
John    HR Meeting 2010-01-01 10:00:00 00:15
John    HR Meeting 2010-01-01 10:30:00 00:30
John    HR Meeting 2010-01-01 11:00:00 00:30
John    HR Meeting 2010-01-01 11:30:00 00:15
John    Lunch      2010-01-01 13:00:00 00:30
John    Lunch      2010-01-01 13:30:00 00:15
John    Training   2010-01-01 15:00:00 00:30
John    Training   2010-01-01 15:30:00 00:20
Mike    HR Meeting 2010-01-01 10:00:00 00:15
Mike    HR Meeting 2010-01-01 10:30:00 00:30
Mike    HR Meeting 2010-01-01 11:00:00 00:30
Mike    HR Meeting 2010-01-01 11:30:00 00:10
Mike    Lunch      2010-01-01 13:00:00 00:25
Mike    Lunch      2010-01-01 13:30:00 00:25
Jessica Training   2010-01-01 11:00:00 00:05
Jessica Training   2010-01-01 11:30:00 00:20

The way data is logged in this table looks little odd. The first 4 records are to be interpreted as "Between 10 and 10.30 AM, John attended 'HR Meeting' for 15 minutes" which means that John attended the meeting at 10:15 sharp. The meeting continued and he attended for another 30 minutes between 10:30 and 11:00. He then attended for another 30 minutes betwee 11:00 and 11:30 and finally attended for 15 minutes between 11:30 and 12:00. So to summarize, it shows that John attended the HR Meeting from 10:15 to 11:45 for 90 minutes.

This is what you need to produce from the above sample data.

Date       EmpName  Activity   SchSt SchDur ActSt ActDur
---------- -------  ---------- ----- ------ ----- ------
2010-01-01 Jessica  Training   11:20 00:30  11:25 00:25
2010-01-01 John     HR Meeting 10:15 01:30  10:15 01:30
2010-01-01 John     Lunch      13:00 00:45  13:00 00:45
2010-01-01 John     Training   15:00 01:00  15:00 00:50
2010-01-01 Mike     HR Meeting 10:15 01:30  10:15 01:25
2010-01-01 Mike     Lunch      13:00 00:45  13:05 00:50

The last four columns are abbreviated and they stand for 'Scheduled Start Time', 'Scheduled Duration', 'Actual Start Time' and 'Actual Duration'.

1. What will be the maximum duration of an activity?
• The maxinum duration of an activity will be 24 hours.
2. Can a record in the Activity Log Table have a duration value that is more than 30 minutes?
• No, the activity log table is updated every 30 minutes and the maximum duration that a record can store is 30 minutes.
3. Does the Activity Log always get recorded at the 00 and 30 minute of the hour? For example, can it run at 10:32?
• The activity log will always update the table at the 00 and 30th minute of every hour. It cannot run at other times, such as 10:32 am.
4. Can an event start on one day and end on the next day?
• No, all events will start and end on the same day
5. What is the sorting requirement of the output?
• Sort the output by Date, Employee Name and Schedule Start Time.
6. Is it possible to have overlapping activities in the appointments table?
• No, one employee can have only one activity scheduled for any given period.
7. Is it possible to have more than one activitiy within 30 minutes in the appointments/activity table?
• Yes, it is acceptable to have one schedule end at 10:15 and another start at 10:15. As long as they are not overlapping, they are fine. However, it is not possible to have two appointments that starts and ends within the span of 30 minutes. For example, the following is not possible: 10:00 to 10:15 Activity1, 10:15 to 10:30 Activity2.
8. Is it possible to have more than one entry per starttime in the activity log?
• Yes, it is possible. A typical example is the case where one appointment ends at 10:15 (which may have started before 10:00) and another starts at 10:15 (which might be ending at some time after 10:30). In such a case there will be two records at 10:00. (Note that in such a case either the first shift should have started earlier than 10:00 or the second should end after 10:30)
9. Is it possible to have Orphan records in either table? Can there be rows in the activity log table without an appointment?
• Yes, it is possible. There may be rows in the activity log table without an appointment. There may be rows in the appointment table without an activity log record. You should ignore such rows while processing.
10. Can the same appointment be scheduled more than once on the same day by the same person?
• NO.
11. Do we have to assume that there is no "gaps" in the activity log table?
• An employee cannot have a break between an activity. So there will be no gaps in the activity table for a single user activity.
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). However, if you see only 15 minutes in the activity log then you should assume that the activity started at 10:15.
13. Can an activity start prior to the scheduled time?
• No, an activity cannot start before the scheduled time. However, it can start late. If an activity is scheduled to start at 10:30 and if you find an activty log entry at 14:00 for 5 minutes, you should assume that the activity started at 14:25.

The early review team had come up with a number of grey areas that need more explanation. One of the scenarios that added a lot of confusion to the processing logic is the case when two activities of an employee are recorded at the same hour slot. This is discussed in FAQ 7, 8 and 12. Here is another illustration that explains this.

Here is the entry in the schedule table

EmpName Activity   StartTime           Duration
------- ---------- ------------------- --------
John    HR Meeting 2010-01-01 10:15:00 01:30
John    Training   2010-01-01 15:00:00 01:00

Activity Log Table Example 1

EmpName Activity   StartTime           Duration
------- ---------- ------------------- --------
John    Training   2010-01-01 16:30:00 00:05' –- 16:55 to 17:00 
John    Training   2010-01-01 17:00:00 00:05' –- 17:00 to 17:05 
John    HR Meeting 2010-01-01 17:00:00 00:10' –- 17:05 to 17:15 

Activity Log Table Example 2

EmpName Activity   StartTime           Duration
------- ---------- ------------------- --------
John    Training   2010-01-01 17:00:00 00:10 –- 17:00 to 17:10 
John    HR Meeting 2010-01-01 17:00:00 00:05 –- 17:25 to 17:30
John    HR Meeting 2010-01-01 17:30:00 00:05 –- 17:30 to 17:35

The above illustration demonstrates how the activity start time should be calculated in the scenarios where two entries for the same employee are recorded at the same 30 minute slot.

Example 1: There are two entries for Trainnig (16:30 and 17:00, 5 minutes each), which indicates that the meeting started in the 16:30 to 17:00 slot and continued to 17:00 to 17:30 slot for 5 minutes. That essentially tells us that the meeting started at 16:55 and ended at 17:05 (10 minutes). HR meeting has no second entry, so it will start at the earliest possible time starting at 17:00 and therefore will follow the Training activity (17:05-17:15).

For the second Example two entries for HR meeting (17:00 and 17:30), so it will be from 17:25 till 17:35 using the same logic we saw earlier. Since Training has no second entry it will start at the earliest possible time which is 17:00.

Here is the script to genreate the source tables and populate the sample data

IF OBJECT_ID('TC24_Schedules','U') IS NOT NULL BEGIN
	DROP TABLE TC24_Schedules
END
GO

CREATE TABLE TC24_Schedules (
	SchID INT IDENTITY PRIMARY KEY,
	EmpName VARCHAR(10),
	Activity VARCHAR(15),
	StartTime SMALLDATETIME,
	Duration VARCHAR(5)
)
GO

INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)
SELECT 'John', 'HR Meeting', '2010-01-01 10:15:00', '01:30' UNION ALL
SELECT 'John', 'Lunch', '2010-01-01 13:00:00', '00:45' UNION ALL
SELECT 'John', 'Training', '2010-01-01 15:00:00', '01:00' UNION ALL
SELECT 'Mike', 'HR Meeting', '2010-01-01 10:15:00', '01:30' UNION ALL
SELECT 'Mike', 'Lunch', '2010-01-01 13:00:00', '00:45' UNION ALL
SELECT 'Jessica', 'Training', '2010-01-01 11:20:00', '00:30' 

IF OBJECT_ID('TC24_ActivityLog','U') IS NOT NULL BEGIN
	DROP TABLE TC24_ActivityLog
END
GO

CREATE TABLE TC24_ActivityLog (
	LogID INT IDENTITY PRIMARY KEY,
	EmpName VARCHAR(10),
	Activity VARCHAR(15),
	StartTime SMALLDATETIME,
	Duration VARCHAR(5)
)
GO

INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)
SELECT 'John', 'HR Meeting', '2010-01-01 10:00:00', '00:15' 
UNION ALL
SELECT 'John', 'HR Meeting', '2010-01-01 10:30:00', '00:30' 
UNION ALL
SELECT 'John', 'HR Meeting', '2010-01-01 11:00:00', '00:30' 
UNION ALL
SELECT 'John', 'HR Meeting', '2010-01-01 11:30:00', '00:15' 
UNION ALL
SELECT 'John', 'Lunch', '2010-01-01 13:00:00', '00:30' 
UNION ALL
SELECT 'John', 'Lunch', '2010-01-01 13:30:00', '00:15' 
UNION ALL
SELECT 'John', 'Training', '2010-01-01 15:00:00', '00:30' 
UNION ALL
SELECT 'John', 'Training', '2010-01-01 15:30:00', '00:20' 
UNION ALL
SELECT 'Mike', 'HR Meeting', '2010-01-01 10:00:00', '00:15' 
UNION ALL
SELECT 'Mike', 'HR Meeting', '2010-01-01 10:30:00', '00:30' 
UNION ALL
SELECT 'Mike', 'HR Meeting', '2010-01-01 11:00:00', '00:30' 
UNION ALL
SELECT 'Mike', 'HR Meeting', '2010-01-01 11:30:00', '00:10' 
UNION ALL
SELECT 'Mike', 'Lunch', '2010-01-01 13:00:00', '00:25' 
UNION ALL
SELECT 'Mike', 'Lunch', '2010-01-01 13:30:00', '00:25' 
UNION ALL
SELECT 'Jessica', 'Training', '2010-01-01 11:00:00', '00:05' 
UNION ALL
SELECT 'Jessica', 'Training', '2010-01-01 11:30:00', '00:20' 

1. The solution should be a single query that starts with a "SELECT", ";WITH" or "WITH"
2. Read the Submission Guidelines and make sure that your solution follows them.
3. The solution should work on SQL Server 2005, 2008 or later versions
4. Use this forum for any questions related to TSQL Challenge #24
5. Last Date to submit your entries: 08 March 2010 Midnight GMT.
6. Please pay attention to the rules explained in the FAQ, especially FAQ 12 which adds a good deal of complexity to the challenge.

In addition to SQL Stars the winner who comes on top will get a license of Red Gate SQL Prompt Pro.

SQL Prompt is a plug-in that increases how fast you can work with SQL.

• Code-completion for SQL Server
• Automated SQL reformatting to match your preferred style
• Rapid access to your database schema information
• Snippets cut down repetitive typing

Click here to download a trial copy of Red Gate SQL Prompt

Special thanks to the Early Review Team, especially lmu92, dishdy, Aurelien Verla, Sergejack and Brad Schulz who reviewed the challenge and provided valuable inputs to make the challenge description and rules more comprehensible.