Kevin, there's some good stuff around about how to make calendar/schedule tables. Anyhow, I have a couple of questions. Do you want to see vacant time slots when there is nothing scheduled in that time slot all week? I might also re-think having separate start and end columns for each day of the week, since this will cause you to have a 'Sunday start', Sunday end', 'Monday start'... The code below will create a schedule for you to mess around with. I went ahead and put the start and end times in one column, but you can easily change that. Let us know if you have any questions.

WITH T1 (n) AS

(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

Tally (n) AS (SELECT n = ROW_NUMBER() OVER (PARTITION BY a.n ORDER BY a.n)

FROM T1 a, T1 b, T1 c),

FullDay (Slots) AS

(SELECT DATEADD(minute,t.n * 15,CAST('00:00:00' AS TIME))

FROM Tally t

WHERE t.n <= 84),

Data AS

(

SELECT

l.LocationName,

s.ScheduleID,

s.LocationID,

DayOfTheWeek = DATENAME(dw,s.DayOfTheWeek),

StartTime = CAST(s.StartTime AS TIME),

EndTime = CAST(s.EndTime AS TIME),

s.ScheduleName

FROM #Schedules s INNER JOIN #Locations l

ON s.LocationId = l.LocationID

)

SELECT

fd.Slots,

LocationName,

ScheduleName,

CASE WHEN DayOfTheWeek = 'Sunday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Sunday,

CASE WHEN DayOfTheWeek = 'Monday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Monday,

CASE WHEN DayOfTheWeek = 'Tuesday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Tuesday,

CASE WHEN DayOfTheWeek = 'Wednesday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Wednesday,

CASE WHEN DayOfTheWeek = 'Thursday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Thursday,

CASE WHEN DayOfTheWeek = 'Friday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Friday,

CASE WHEN DayOfTheWeek = 'Saturday' THEN CONVERT(CHAR(5), d.StartTime) + ' - ' +

CONVERT(CHAR(5), d.EndTime)

ELSE NULL

END AS Saturday

FROM FullDay fd LEFT OUTER JOIN Data d

ON fd.Slots = CAST(d.StartTime AS TIME)

WHERE fd.Slots >= '09:00:00'

AND fd.Slots <= '21:00:00'