Selecting non-overlapping Date Ranges

• Mark Cowne

  One Orange Chip

  Points: 26986

  More actions

  September 3, 2019 at 3:14 pm

  Answer

  #3676907

  This is a gaps and islands problem

  
  WITH Src AS (
  SELECT SampleDate, SampleValue,
  ROW_NUMBER() OVER(ORDER BY SampleDate) -
  ROW_NUMBER() OVER(PARTITION BY SampleValue ORDER BY SampleDate) AS grp
  FROM #SampleRanges
  )
  SELECT MIN(SampleDate) AS RangeStartDate,
  MAX(SampleDate) AS RangeEndDate,
  SampleValue,
  COUNT(*) AS ValueCount
  FROM Src
  GROUP BY grp,SampleValue
  ORDER BY RangeStartDate;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• tom 69406

  SSC-Addicted

  Points: 446

  More actions

  September 3, 2019 at 3:26 pm

  #3676911

  Brilliant.  Great solution

  I knew I'd come across this before, but "gaps and islands" are terms I'd forgotten , so I can now search properly for this type of problem.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 739545

  More actions

  September 4, 2019 at 9:04 am

  #3677117

  https://www.sqlservercentral.com/search/gaps+and+islands