I have to agree with Greg that TIME data type is much easier to work with in this case. So your DDL would be:

CREATE TABLE #Locations (

LocationIDINTEGER NOT NULL

,LocationNameVARCHAR(100) NOT NULL

)

CREATE TABLE #Schedules (

ScheduleIDINTEGER NOT NULL

,LocationIDINTEGER NOT NULL

,DayOfTheWeekINTEGER NOT NULL

,StartTimeTIME

,EndTimeTIME

,ScheduleNameVARCHAR(100) NOT NULL

)

Then the same sample data (the 1899 dates will get saved as TIME just fine) and then this (alternate solution), which works similarly to what Greg has done.

;WITH Schedule (TimeSlot) AS (

SELECT CAST(CAST(DATEADD(minute, 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), '09:00') AS TIME) AS VARCHAR(5))

FROM (VALUES ($),($),($),($),($),($),($)) a(n)

,(VALUES ($),($),($),($),($),($),($)) b(n)) -- exactly 49 time slots are needed 09:00 - 21:00

SELECT TimeSlot

,ScheduleName

,LocationName

,Sunday=MAX(CASE WHEN DayOfTheWeek = 1 THEN TimePeriod END)

,Monday=MAX(CASE WHEN DayOfTheWeek = 2 THEN TimePeriod END)

,Tuesday=MAX(CASE WHEN DayOfTheWeek = 3 THEN TimePeriod END)

,Wednesday=MAX(CASE WHEN DayOfTheWeek = 4 THEN TimePeriod END)

,Thursday=MAX(CASE WHEN DayOfTheWeek = 5 THEN TimePeriod END)

,Friday=MAX(CASE WHEN DayOfTheWeek = 6 THEN TimePeriod END)

,Saturday=MAX(CASE WHEN DayOfTheWeek = 7 THEN TimePeriod END)

FROM Schedule a

LEFT OUTER JOIN #Schedules c ON a.TimeSlot = c.StartTime

LEFT OUTER JOIN #Locations d ON c.LocationID = d.LocationID

CROSS APPLY (SELECT CAST(StartTime AS VARCHAR(5)) + ' - ' + CAST(StartTime AS VARCHAR(5))) b(TimePeriod)

GROUP BY TimeSlot, ScheduleName, LocationName

DROP TABLE #Locations

DROP TABLE #Schedules

My personal preferences in cases like this is to combine the calendar (time slot) generation with the Tally table. And I'm also using a different form of a Tally table, just to show you that you have many options.

I also through in a CROSS APPLY to create TimePeriod, which significantly shortens the code.