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)
{
if (ThisTypeOfLeave == PreviousTypeOfLeave)
{
TimeSpan span = ThisDate.Subtract(PreviousDate);
if (span.Days == 1)
{
string EventID = Reader[0].ToString();
EventIDs.Add(EventID);
PreviousDate = ThisDate;
ToDate = (DateTime)Reader[3];
}
else
{
if (ToDate != DateTime.MaxValue)
{
UpdateStartEnd(StartEventID, ToDate);
}
PreviousDate = ThisDate;
ToDate = DateTime.MaxValue;
StartEventID = Reader[0].ToString();
}
}
else
{
PreviousTypeOfLeave = ThisTypeOfLeave;
}
}
else
{
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.