Group consecutive lines of the same value using time intervals

Question

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

Give the following schedule table for the class:

╔═══════════╦════════════╦═══════════╦═══════════╦═════════╗ ║ 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 a 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 look at a schedule showing which cry uses which class for a certain period of time ...

My initial thought: Group by Classroom and CourseName and take Max and Min for start\end time, but this will not give me time, it will show as if Course 1 used Classroom from 08:00 - 16:00 without a break in the middle.

Answer 1

The query defines each EndTime line with NOT EXISTS to ensure that no other class or course of a different type is planned between the StartTime and EndTime course range, and then uses MIN and GROUP BY to find StartTime .

The NOT EXISTS ensures that there are no gaps between the StartTime and EndTime by searching for any lines that have an EndTime between StartTime and EndTime but belong to different CourseName or CourseRoom .

 SELECT t0.ClassRoom, t0.CourseName, MIN(t0.StartTime), t0.EndTime FROM ( SELECT t1.ClassRoom, t1.CourseName, t1.StartTime, ( SELECT MAX(t2.EndTime) FROM tableA t2 WHERE t2.CourseName = t1.CourseName AND t2.ClassRoom = t1.ClassRoom AND NOT EXISTS (SELECT 1 FROM tableA t3 WHERE t3.EndTime < t2.EndTime AND t3.EndTime > t1.EndTime AND (t3.CourseName <> t2.CourseName OR t3.ClassRoom <> t2.ClassRoom) ) ) EndTime FROM tableA t1 ) t0 GROUP BY t0.ClassRoom, t0.CourseName, t0.EndTime 

http://www.sqlfiddle.com/#!6/39d4b/9

Answer 2

If you are using SQLServer 2012 or better, you can use the LAG to get the previous column value, and then SUM() OVER (ORDER BY ...) to create the current amount, in this case, which takes into account the change in the course name, which can be used as an anchor GROUP BY

 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

Answer 3

  CREATE TABLE Classroom(Classroom VARCHAR(100), CourseName VARCHAR(100), Lesson VARCHAR(100), StartTime VARCHAR(100), EndTime VARCHAR(100)) INSERT INTO Classroom SELECT '1001','Course 1','Lesson 1 ','0800','0900' UNION SELECT '1001','Course 1','Lesson 2 ','0900','1000' UNION SELECT '1001','Course 1','Lesson 3 ','1000','1100' UNION SELECT '1001','Course 2','Lesson 10','1100','1200' UNION SELECT '1001','Course 2','Lesson 11','1200','1300' UNION SELECT '1001','Course 1','Lesson 4 ','1300','1400' UNION SELECT '1001','Course 1','Lesson 5 ','1400','1500' SELECT * FROM Classroom ;WITH CTE_ClassRooms AS ( SELECT *,ROW_NUMBER() over(partition by classroom,CourseName order by StartTime) AS R FROM Classroom A WHERE NOT EXISTS(SELECT 1 FROM Classroom B WHERE B.Classroom = A.Classroom AND B.CourseName = A.CourseName AND B.StartTime = A.EndTime) UNION ALL SELECT B.*,R fROM CTE_ClassRooms A JOIN Classroom B ON B.Classroom = A.Classroom AND B.CourseName = A.CourseName AND A.StartTime = B.EndTime ) --SELECT * FROM CTE_ClassRooms order by Classroom,CourseName,R SELECT Classroom,CourseName,MIN(StartTime),MAX(EndTime) FROM CTE_ClassRooms GROUP BY Classroom,CourseName,R