title: connecting records regarding their start and end dates
body: <p>I have the following records:</p>
  <pre><code>+----+------------------+-----------------+-------+
  | ID | StartDate        | EndDate         | Color |
  +====+==================+=================+=======+
  | 1  | 4/14/22 12:00 AM | 4/14/22 2:45 AM | R     |
  +----+------------------+-----------------+-------+
  | 1  | 4/14/22 3:40 AM  | 4/14/22 7:06 AM | R     |
  +----+------------------+-----------------+-------+
  | 1  | 4/14/22 7:06 AM  | 4/14/22 7:56 AM | R     |
  +----+------------------+-----------------+-------+
  | 1  | 4/14/22 7:56 AM  | 4/14/22 8:00 AM | R     |
  +----+------------------+-----------------+-------+
  | 1  | 4/14/22 7:56 AM  | 4/14/22 8:00 AM | R     |
  +----+------------------+-----------------+-------+
  | 2  | 4/14/22 12:00 AM | 4/14/22 4:40 AM | R     |
  +----+------------------+-----------------+-------+
  | 2  | 4/14/22 4:40 AM  | 4/14/22 6:00 AM | G     |
  +----+------------------+-----------------+-------+
  | 2  | 4/14/22 6:00 AM  | 4/14/22 8:00 AM | G     |
  +----+------------------+-----------------+-------+
  </code></pre>
  <p>and I want to have an output to:
  merge rows that are following each other (where prev.EndDate = cur.StartDate) if the colors are same.</p>
  <p>the output should look like:</p>
  <pre><code>+----+------------------+-----------------+-------+
  | ID | StartDate        | EndDate         | Color |
  +====+==================+=================+=======+
  | 1  | 4/14/22 12:00 AM | 4/14/22 2:45 AM | R     |
  +----+------------------+-----------------+-------+
  | 1  | 4/14/22 3:40 AM  | 4/14/22 8:00 AM | R     |
  +----+------------------+-----------------+-------+
  | 2  | 4/14/22 12:00 AM | 4/14/22 4:40 AM | R     |
  +----+------------------+-----------------+-------+
  | 2  | 4/14/22 4:40 AM  | 4/14/22 8:00 AM | G     |
  +----+------------------+-----------------+-------+
  </code></pre>
  <p>I tried with lead/lag and cte but couldn't manage. any ideas?</p>
  <p>sample data:</p>
  <pre><code>declare @color table (
      ID int,
      StartTime datetime,
      EndTime datetime,
      Color char(1)
  )
  
  insert @color values (2707670, '2022-04-14 00:00:00.000','2022-04-14 02:45:00.000', 'R')
  insert @color values (2707670, '2022-04-14 03:40:00.000','2022-04-14 07:06:28.977', 'R')
  insert @color values (2707670, '2022-04-14 07:06:28.977','2022-04-14 07:56:25.600', 'R')
  insert @color values (2707670, '2022-04-14 07:56:25.600','2022-04-14 08:00:00.000', 'R')
  </code></pre>
  <p>thank in advance.</p>