sql - Group consecutive rows of same value using time spans

Question

Sorry for the vague title (I just don't know how to describe this conundrum)

Give the following schedule table for a classroom:

╔═══════════╦════════════╦═══════════╦═══════════╦═════════╗
║ Classroom ║ CourseName ║  Lesson   ║ StartTime ║ EndTime ║
╠═══════════╬════════════╬═══════════╬═══════════╬═════════╣
║      1001 ║ Course 1   ║ Lesson 1  ║      0800 ║    0900 ║
║      1001 ║ Course 1   ║ Lesson 2  ║      0900 ║    1000 ║
║      1001 ║ Course 1   ║ Lesson 3  ║      1000 ║    1100 ║
║      1001 ║ Course 2   ║ Lesson 10 ║      1100 ║    1200 ║
║      1001 ║ Course 2   ║ Lesson 11 ║      1200 ║    1300 ║
║      1001 ║ Course 1   ║ Lesson 4  ║      1300 ║    1400 ║
║      1001 ║ Course 1   ║ Lesson 5  ║      1400 ║    1500 ║
╚═══════════╩════════════╩═══════════╩═══════════╩═════════╝

I would like to group the table to display this:

╔═══════════╦════════════╦═══════════╦═════════╗
║ Classroom ║ CourseName ║ StartTime ║ EndTime ║
╠═══════════╬════════════╬═══════════╬═════════╣
║      1001 ║ Course 1   ║      0800 ║    1100 ║
║      1001 ║ Course 2   ║      1100 ║    1300 ║
║      1001 ║ Course 1   ║      1300 ║    1500 ║
╚═══════════╩════════════╩═══════════╩═════════╝

Basically we are looking at a schedule that show which crouse is using what classroom during a certain timespan...

My initial thought was: Group by Classroom and CourseName and take Max and Min for startend time but that will not give me the time spans it will show as if Course 1 is using the Classroom from 08:00 - 16:00 with no break in the middle.

Answer 1

If you're using SQLServer 2012 or better you can use LAG to get the previous value of a column, then SUM() OVER (ORDER BY ...) to create a rolling sum, in this case one that count the change of the CourseName, that can be used as the GROUP BY anchor

With A AS (
  SELECT ClassRoom
       , CourseName
       , StartTime
       , EndTime
       , PrevCourse = LAG(CourseName, 1, CourseName) OVER (ORDER BY StartTime)
  FROM   Table1
), B AS (
  SELECT ClassRoom
       , CourseName
       , StartTime
       , EndTime
       , Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
                OVER (ORDER BY StartTime, CourseName)
  FROM   A
)
SELECT ClassRoom
     , CourseName
     , MIN(StartTime) StartTime
     , MAX(EndTime) EndTime
FROM   B
GROUP BY ClassRoom, CourseName, Ranker
ORDER BY StartTime

SQLFiddle demo