Collapse Adjacent Dates in Table

See more:

C#

SQL

I had to go in and do it "off-line", rather than using SQL. No biggie, since I had to run an executable on it anyway to get the data in the table initially.

Here's the code, in case anyone is interested (or sees a big booboo)(it works, though, so the booboo can't be all that bad ;P ): (un-refactored & ugly)

static void CollapseConsecutiveDates()
{
  List<string> EventIDs = new List<string>();
  string SQLString = "select EventID, FromDate, Employee, ToDate, TypeOfLeave from Leave order by Employee, TypeOfLeave, FromDate";
  SqlCommand sqlComm = new SqlCommand(SQLString, sqlConn);
  SqlDataReader Reader = sqlComm.ExecuteReader();

  DateTime PreviousDate = DateTime.MinValue;
  DateTime ThisDate = DateTime.MaxValue;
  DateTime ToDate = DateTime.MaxValue;
  string PreviousEmployee = "";
  string ThisEmployee = "";
  string PreviousTypeOfLeave = "";
  string ThisTypeOfLeave = "";
  string StartEventID = "";
  while (Reader.Read())
  {
    ThisEmployee = Reader[2].ToString();
    ThisTypeOfLeave = Reader[4].ToString();
    ThisDate = (DateTime)Reader[1];
    if (ThisEmployee == PreviousEmployee)  // same employee, part of a series
    {
      if (ThisTypeOfLeave == PreviousTypeOfLeave)  //then check for consecutive dates
      {
        TimeSpan span = ThisDate.Subtract(PreviousDate);
        if (span.Days == 1)  //Yes, a consecutive date and leavetype
        {
          string EventID = Reader[0].ToString();
          EventIDs.Add(EventID);
          PreviousDate = ThisDate;
          ToDate = (DateTime)Reader[3];
        }
        else  //same employee, new date series
        {
          if (ToDate != DateTime.MaxValue)
          {
            UpdateStartEnd(StartEventID, ToDate);  //This is the goal!
          }
          PreviousDate = ThisDate;
          ToDate = DateTime.MaxValue;
          StartEventID = Reader[0].ToString();
        }
      }
      else  //same employee, new type series
      {
        PreviousTypeOfLeave = ThisTypeOfLeave;
      }
    }
    else  //fresh employee series
    {
      PreviousEmployee = ThisEmployee;
      StartEventID = Reader[0].ToString();
      PreviousDate = ThisDate;
      PreviousTypeOfLeave = ThisTypeOfLeave;
      ToDate = DateTime.MaxValue;
    }
  }
  Reader.Close();
  foreach (string s in EventIDs)
  {
    SQLString = "delete from Leave where EventID = " + s;
    sqlComm.CommandText = SQLString;
    sqlComm.ExecuteNonQuery();
  }
}

original question:

I have a table with employees and dates that looks like this (all fields of obvious types):

154 BOB FRONT OFFICE    11/22/2010 7:30:00 AM   11/22/2010 4:30:00 PM
155 BOB FRONT OFFICE    11/23/2010 7:30:00 AM   11/23/2010 4:30:00 PM
156 BOB FRONT OFFICE    11/24/2010 7:30:00 AM   11/24/2010 4:30:00 PM

I need to get it to look like this:

154 BOB FRONT OFFICE    11/22/2010 7:30:00 AM   11/24/2010 4:30:00 PM

i.e., collapse adjacent dates and delete the rows no longer needed.

Can this be done in SQL itself, or will I have to run an external process on the table before use?

I think the latter, but before I get too far into it, I just want to double-check if there's a "native" SQL way. (MS SQL 2005, if it matters).

If not, I'll be updating this question with the C# code I have so far...ugly, brute-force code.