Lutz Mueller, an active participant of the Early Review Team has built a tricky data set for TSQL Challenge 24. It is recommended that you test your solutions against this data set before submitting your solutions.
Schedule Table
INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)
SELECT 'John','HR Meeting','2010-01-02 09:55','00:20' UNION ALL
SELECT 'John','Training1','2010-01-02 10:15','00:15' UNION ALL
SELECT 'John','Project Mtg1','2010-01-02 10:30','00:30' UNION ALL
SELECT 'John','Training2','2010-01-02 11:00','01:00' UNION ALL
SELECT 'John','Lunch','2010-01-02 12:00','00:45' UNION ALL
SELECT 'John','Project Mtg2','2010-01-02 12:45','00:45' UNION ALL
SELECT 'John','Project Mtg2','2010-01-03 09:00','01:30' UNION ALL
SELECT 'John','Project Mtg1','2010-01-03 10:30','00:30' UNION ALL
SELECT 'John','HR Meeting','2010-01-03 11:00','01:00' UNION ALL
SELECT 'Jessica','HR Meeting','2010-01-02 09:55','00:20' UNION ALL
SELECT 'Jessica','Training1','2010-01-02 10:15','00:15' UNION ALL
SELECT 'Jessica','Project Mtg1','2010-01-02 10:30','00:30' UNION ALL
SELECT 'Jessica','Training2','2010-01-02 11:00','01:00' UNION ALL
SELECT 'Jessica','Lunch','2010-01-02 12:00','00:45' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-02 12:45','00:45' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-03 09:00','01:30' UNION ALL
SELECT 'Jessica','Project Mtg1','2010-01-03 10:30','00:30' UNION ALL
SELECT 'Jessica','HR Meeting','2010-01-03 11:00','01:00'
Activity Log Table
INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)
SELECT 'John','HR Meeting','2010-01-02 09:30:00','00:05' UNION ALL
SELECT 'John','HR Meeting','2010-01-02 10:00:00','00:15' UNION ALL
SELECT 'John','Training1','2010-01-02 10:00:00','00:15' UNION ALL
SELECT 'John','Project Mtg1','2010-01-02 10:30:00','00:30' UNION ALL
SELECT 'John','Project Mtg1','2010-01-02 11:00:00','00:10' UNION ALL
SELECT 'John','Project Mtg3','2010-01-02 11:00:00','00:20' UNION ALL
SELECT 'John','Project Mtg3','2010-01-02 11:30:00','00:30' UNION ALL
SELECT 'John','Lunch','2010-01-02 12:00:00','00:30' UNION ALL
SELECT 'John','Lunch','2010-01-02 12:30:00','00:15' UNION ALL
SELECT 'John','Project Mtg3','2010-01-02 12:30:00','00:15' UNION ALL
SELECT 'John','Project Mtg3','2010-01-02 13:00:00','00:30' UNION ALL
SELECT 'John','Project Mtg3','2010-01-02 13:30:00','00:30' UNION ALL
SELECT 'John','Project Mtg2','2010-01-02 14:00:00','00:30' UNION ALL
SELECT 'John','Project Mtg2','2010-01-02 14:30:00','00:30' UNION ALL
SELECT 'John','Project Mtg2','2010-01-02 15:00:00','00:15' UNION ALL
SELECT 'John','Project Mtg2','2010-01-03 09:00:00','00:15' UNION ALL
SELECT 'John','Project Mtg2','2010-01-03 09:30:00','00:30' UNION ALL
SELECT 'John','Project Mtg2','2010-01-03 10:00:00','00:30' UNION ALL
SELECT 'John','Project Mtg1','2010-01-03 10:30:00','00:30' UNION ALL
SELECT 'John','Training2','2010-01-03 11:00:00','00:30' UNION ALL
SELECT 'John','Training2','2010-01-03 11:30:00','00:30' UNION ALL
SELECT 'Jessica','HR Meeting','2010-01-02 10:00:00','00:20' UNION ALL
SELECT 'Jessica','Training1','2010-01-02 10:00:00','00:10' UNION ALL
SELECT 'Jessica','Training1','2010-01-02 10:30:00','00:05' UNION ALL
SELECT 'Jessica','Project Mtg1','2010-01-02 10:30:00','00:25' UNION ALL
SELECT 'Jessica','Training2','2010-01-02 11:00:00','00:30' UNION ALL
SELECT 'Jessica','Training2','2010-01-02 11:30:00','00:30' UNION ALL
SELECT 'Jessica','Training2','2010-01-02 12:00:00','00:15' UNION ALL
SELECT 'Jessica','Lunch','2010-01-02 12:00:00','00:15' UNION ALL
SELECT 'Jessica','Lunch','2010-01-02 12:30:00','00:15' UNION ALL
SELECT 'Jessica','Project Mtg3','2010-01-02 12:30:00','00:15' UNION ALL
SELECT 'Jessica','Project Mtg3','2010-01-02 13:00:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg3','2010-01-02 13:30:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-02 14:00:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-02 14:30:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-02 15:00:00','00:15' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-03 09:00:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-03 09:30:00','00:30' UNION ALL
SELECT 'Jessica','Project Mtg2','2010-01-03 10:00:00','00:30'
Expected Results
Date EmpName Activity SchSt SchDur ActSt ActDur
2010-01-02 Jessica HR Meeting 09:55 00:20 10:00 00:20
2010-01-02 Jessica Training1 10:15 00:15 10:20 00:15
2010-01-02 Jessica Project Meeting1 10:30 00:30 10:35 00:25
2010-01-02 Jessica Training2 11:00 01:00 11:00 01:15
2010-01-02 Jessica Lunch 12:00 00:45 12:15 00:30
2010-01-02 Jessica Project Meeting2 12:45 00:45 14:00 01:15
2010-01-02 John HR Meeting 09:55 00:20 09:55 00:20
2010-01-02 John Training1 10:15 00:15 10:15 00:15
2010-01-02 John Project Meeting1 10:30 00:30 10:30 00:40
2010-01-02 John Lunch 12:00 00:45 12:00 00:45
2010-01-02 John Project Meeting2 12:45 00:45 14:00 01:15
2010-01-03 Jessica Project Meeting2 09:00 01:30 09:00 01:30
2010-01-03 John Project Meeting2 09:00 01:30 09:15 01:15
2010-01-03 John Project Meeting1 10:30 00:30 10:30 00:30
If you would like to build your own version of the tricky data and make it available to the community, let us know. You can either send is the tricky data or post it in your own blog/website and send us the link.