Use the ROW_NUMBER() function to work out which row the information should be on.
Also, PIVOT tends to be more readable than lots of CASE statements.
Try something like:
SELECT SYear, SWeek
,[2] AS Monday
,[3] As Tuesday
,[4] AS Wednesday
,[5] AS Thursday
FROM
(
SELECT
DATEPART(year, ScheduleStart) AS SYear
,DATEPART(week, ScheduleStart) AS SWeek
,DATEPART(weekday, ScheduleStart)AS SWeekday
,ROW_NUMBER()
OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, ScheduleStart), 0) ORDER BY ScheduleStart, ScheduleGUID) AS RowNum
,CONVERT(varchar(5), ScheduleStart, 108) + ' to ' + CONVERT(varchar(5), ScheduleEnd, 108) AS Period
FROM Schedule
) D
PIVOT
(
MIN(Period)
FOR SWeekDay IN ([2], [3], [4], [5])
)
AS P