-- This query works, but we can save a lot of code using LEAD and LAG functions
SELECT 
	t.EmployeeName
,	t.ShiftName
,	t.ShiftDate
,	(
		SELECT TOP 1 
			ShiftDate 
		FROM EmployeeShift AS tlead
		WHERE 
			tlead.EmployeeName = t.EmployeeName
		AND t.ShiftName = tlead.ShiftName
		AND tlead.ShiftDate > t.ShiftDate
		ORDER BY 
			ShiftDate
	) AS NextShift
FROM
	EmployeeShift AS t
WHERE
	t.ShiftName = 'Night'
AND	t.ShiftDate = '2015-03-01'
ORDER BY
	t.EmployeeName ASC;





-- Example of how to use LEAD and LAG functions at the same time.
WITH Shifts
AS
(
	SELECT
		EmployeeName
	,	ShiftName
	,	ShiftDate
	,	LEAD(ShiftDate,1)	OVER(PARTITION BY EmployeeName, ShiftName ORDER BY ShiftDate asc) NextNightShift
	,	ROW_NUMBER()		OVER(PARTITION BY EmployeeName, ShiftName ORDER BY ShiftDate asc) RowNum
	FROM
		dbo.EmployeeShift
)
SELECT 
	EmployeeName
,	ShiftName
,	ShiftDate
,	NextNightShift
FROM 
	Shifts 
WHERE 
	RowNum = 1
AND	ShiftName = 'Night'
AND ShiftDate = '2015-03-01'
ORDER BY
	EmployeeName ASC;

-- Using LAG
WITH Shifts
AS
(
	SELECT
		EmployeeName
	,	ShiftName
	,	ShiftDate
	,	LAG(ShiftName,1)	OVER(PARTITION BY employeename ORDER BY ShiftDate desc) PreviousShift
	FROM
		dbo.EmployeeShift
)
SELECT 
	EmployeeName
,	ShiftName
,	ShiftDate
,	PreviousShift
FROM 
	Shifts 
WHERE 
	ShiftDate = '2015-03-24'
ORDER BY
	EmployeeName ASC