Group consecutive rows of same value using time spans

by

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

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Not equal != operator on NULL
  4. SET NOCOUNT ON usage
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Subquery using Exists 1 or Exists *
  8. Truncate (not round) decimal places in SQL Server
  9. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  10. Is it possible to specify condition in Count()?
  11. Update records in table from CTE
  12. Dynamic Pivot Columns in SQL Server
  13. Is it possible to select sql server data using column ordinal position
  14. Selecting N rows in SQL Server
  15. how can I Update top 100 records in sql server
  16. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  17. How can I INSERT data into two tables simultaneously in SQL Server?
  18. SQL “select where not in subquery” returns no results
  19. How can a LEFT OUTER JOIN return more records than exist in the left table?
  20. Count Number of Consecutive Occurrence of values in Table
  21. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  22. Pivot data in T-SQL
  23. Column name or number of supplied values does not match table definition
  24. Split one column value into multiple column values
  25. T-SQL – Aliasing using “=” versus “as” [closed]
  26. SQL Query – Concatenating Results into One String [duplicate]
  27. How to escape square brackets inside square brackets for field name
  28. T-SQL Skip Take Stored Procedure
  29. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  30. MAX vs Top 1 – which is better?

Leave a Comment