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

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

	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
	;

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

	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' 

/*
=============================================================================================
CREATE DATE: 03/14/2010
 CREATED BY: SETH PHELABAUM
    PURPOSE: TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees
     ISSUES: 
      NOTES:	
Revision History
Date	 By		Change Made
-------- ---  	-------------------------------------
=============================================================================================
*/

;WITH
AppOrder AS (			-- Use this to get a single line entry for each appointment so I can use it in the next sort
	SELECT *, ROW_NUMBER() OVER (PARTITION BY SDate, A.Empname ORDER BY StartTime) AppSOrder
	FROM (SELECT cast(starttime as date) SDate, empname, Activity, StartTime from TC24_Schedules) A
),
ActualSeq AS (			-- Sequence the appointments table per schedule
	SELECT AL.*, ROW_NUMBER() OVER (PARTITION BY AL.SDate, AL.EmpName ORDER BY A.AppSOrder, AL.StartTime) AppSeq
	FROM (	SELECT cast(AL.starttime as date) SDate, AL.empname, AL.Activity, AL.StartTime 
			FROM TC24_ActivityLog AL) AL
				INNER JOIN AppOrder A ON AL.SDate = A.SDate AND AL.EmpName = A.EmpName AND AL.Activity = A.Activity
),
Actuals AS (			-- Combine the above Actual CTE's into 1
SELECT AL.Activity, AL.Duration, DATEPART(N,AL.Duration) Dmin, AL.EmpName, cast(AL.starttime as date) SDate, cAct.AppSeq, AL.StartTime
FROM TC24_ActivityLog AL
	INNER JOIN ActualSeq cAct ON CAST(AL.StartTime as DATE) = cAct.SDate and AL.EmpName = cAct.EmpName and AL.Activity = cAct.Activity
				AND AL.StartTime = cAct.StartTime
),
Scheduled AS (												-- Combine the above Schedule CTE's into 1
SELECT S.Activity, S.Duration, DATEPART(N,S.Duration) Dmin, S.EmpName, CAST(S.StartTime as DATE) SDate, S.StartTime,
	LEFT(cast(S.StartTime as time),5) SchSt
FROM TC24_Schedules S
),
ActDur AS (													-- Calculate the total time actually spent in the appointment
SELECT A.EmpName, A.SDate, A.Activity, LEFT(CAST(DATEADD(n,SUM(A.DMin),0) as time),5) ActDur
FROM Actuals A
GROUP BY A.EmpName, A.SDate, A.Activity, activity
),
Final AS (
SELECT S.SDate, S.EmpName, S.Activity, S.SchSt, S.Duration SchDur, AD.ActDur,
--					-- Start and goes into next.
	CASE WHEN	A.Activity = ISNULL(B.Activity,'')			-- Next Window is the same appointment
			THEN DATEADD(n,A.DMin * -1, B.StartTime)		-- Subtract A minutes from B Minutes.
					-- Continuing from Previous
		 WHEN	A.Activity <> ISNULL(B.Activity,'')			-- Next Window is not the same appointment
			AND A.Activity = ISNULL(C.Activity,'')			-- Same as Previous Appt, 
			THEN A.StartTime								-- set to A.StartTime (Will be discarded anyways)
					-- Does not span time periods
		WHEN	A.Activity <> ISNULL(B.Activity,'')			-- Next Window is not the same appointment
			AND A.Activity <> ISNULL(C.Activity,'')			-- Previous window is not the same (Standalone)	
			THEN
				CASE WHEN C.Activity IS NULL				-- First Scheduled Activity of the Day
						THEN CASE WHEN S.StartTime > A.StartTime 
									THEN S.StartTime
								ELSE A.StartTime
							 END
					 WHEN A.StartTime = ISNULL(C.StartTime,0)	-- Previous Activity Bled into this period.
						THEN CASE WHEN S.StartTime > DATEADD(n,C.DMin, A.StartTime) 
									THEN S.StartTime		-- Use scheduled time if it is still greater
								ELSE DATEADD(n,C.DMin, A.StartTime) -- Use earliest possible time
								END
					 WHEN S.StartTime > A.StartTime			-- Use Scheduled Time if none of the above exceptions apply 
						THEN S.StartTime					-- and it is greater than the period start time
				ELSE A.StartTime
				END
			ELSE NULL
			END ActSch
FROM Scheduled S
	INNER JOIN ActDur AD ON S.EmpName = AD.EmpName AND S.SDate = AD.SDate AND S.Activity = AD.Activity
	LEFT JOIN  Actuals A ON A.EmpName = S.EmpName AND S.SDate = A.SDate AND A.Activity = S.Activity
	LEFT JOIN  Actuals B ON A.EmpName = B.EmpName AND S.SDate = B.SDate AND A.AppSeq = B.AppSeq - 1
	LEFT JOIN  Actuals C ON A.EmpName = C.EmpName AND S.SDate = C.SDate AND A.AppSeq = C.AppSeq + 1)
	-- Final Sel
Select SDate, EmpName, Activity, SchSt, SchDur, LEFT(cast(MIN(ActSch) as time),5)  ActSt, ActDur
from Final
GROUP BY SDate, EmpName, Activity, SchSt, SchDur, ActDur
ORDER BY SDate, EmpName, SchSt