Lutz has submitted another tricky scenario where there are 3 records in the activity log for the same 30 minute slot.
Schedule Table
INSERT INTO TC24_Schedules (EmpName, Activity, StartTime, Duration)
SELECT 'Tom', 'HR Meeting', '2010-01-04 09:30:00', '00:15'
UNION ALL
SELECT 'Tom', 'Training1', '2010-01-04 09:45:00', '00:30'
UNION ALL
SELECT 'Tom', 'Lunch', '2010-01-04 10:15:00', '00:45'
Activity Log
INSERT INTO TC24_ActivityLog(EmpName, Activity, StartTime, Duration)
SELECT 'Tom', 'Training1', '2010-01-04 09:30:00', '00:15'
UNION ALL
SELECT 'Tom', 'Training1', '2010-01-04 10:00:00', '00:10'
UNION ALL
SELECT 'Tom', 'HR Meeting', '2010-01-04 10:00:00', '00:15'
UNION ALL
SELECT 'Tom', 'Lunch', '2010-01-04 10:00:00', '00:05'
UNION ALL
SELECT 'Tom', 'Lunch', '2010-01-04 10:30:00', '00:30'
Expected Result
Date EmpName activity SchSt SchDur ActSt ActDur
2010-01-04 Tom HR Meeting 09:30 00:15 10:10 00:15
2010-01-04 Tom Training1 09:45 00:30 09:45 00:25
2010-01-04 Tom Lunch 10:15 00:45 10:25 00:35
Please note that there are no changes in the rules. This scenario is created based on the rules already published in the challenge. The purpose of this post is to help you test your solutions with yet another tricky scenario and make sure that it succeeds the test.
Thanks Lutz for building and submitting this. If some of you have already created tricky scenarios to test your solutions, we will be very happy to publish them on your behalf. Alternatively, you can publish it on your blog/website and share a link here.