Database Challenges

Here is the basic testing data for TSQL Challenge 24

Appointments Table

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

Activity Logs Table

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

Expected Output

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

Script

Use the below script to generate the source tables and populate them with 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'