SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Table1
    ([Classroom] int
   , [CourseName] varchar(8)
   , [Lesson] varchar(9)
   , [StartTime] char(4)
   , [EndTime] char(4))
;
    
INSERT INTO Table1
    ([Classroom], [CourseName], [Lesson], [StartTime], [EndTime])
VALUES
    (1001, 'Course 1', 'Lesson 1', '0800', '0900'),
    (1001, 'Course 1', 'Lesson 2', '0900', '1000'),
    (1001, 'Course 1', 'Lesson 3', '1000', '1100'),
    (1001, 'Course 1', 'Lesson 6', '1100', '1200'),
    (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')
;
8 rows affected

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
       , CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END flag
       , Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
                OVER (ORDER BY StartTime, CourseName)
  FROM   A
)

SELECT B.* FROM B;


ClassRoom
CourseName
StartTime
EndTime
flag
Ranker


1001
Course 1
0800
0900
0
0

1001
Course 1
0900
1000
0
0

1001
Course 1
1000
1100
0
0

1001
Course 1
1100
1200
0
0

1001
Course 2
1100
1200
1
1

1001
Course 2
1200
1300
0
1

1001
Course 1
1300
1400
1
2

1001
Course 1
1400
1500
0
2


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
       , CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END flag
       , Ranker = SUM(CASE WHEN CourseName = PrevCourse THEN 0 ELSE 1 END)
                  OVER (PARTITION BY CourseName ORDER BY StartTime)
  FROM   A
)

SELECT B.* FROM B;


ClassRoom
CourseName
StartTime
EndTime
flag
Ranker


1001
Course 1
0800
0900
0
0

1001
Course 1
0900
1000
0
0

1001
Course 1
1000
1100
0
0

1001
Course 1
1100
1200
0
0

1001
Course 1
1300
1400
1
1

1001
Course 1
1400
1500
0
1

1001
Course 2
1100
1200
1
1

1001
Course 2
1200
1300
0
1

ClassRoom	CourseName	StartTime	EndTime	flag	Ranker
1001	Course 1	0800	0900	0	0
1001	Course 1	0900	1000	0	0
1001	Course 1	1000	1100	0	0
1001	Course 1	1100	1200	0	0
1001	Course 2	1100	1200	1	1
1001	Course 2	1200	1300	0	1
1001	Course 1	1300	1400	1	2
1001	Course 1	1400	1500	0	2