Click here to Skip to main content
14,644,614 members
Rate this:
Please Sign up or sign in to vote.
See more:
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.
Posted
Updated 29-Dec-10 8:52am
v2
Comments
AspDotNetDev 29-Dec-10 16:23pm
   
FYI, SQL has cursors, which are a bit like C# for loops. It also has variables. Your C# code can definitely be done in SQL, and much faster than the code you have here (e.g., issuing a delete command for each row to delete is inefficient).
GenJerDan 29-Dec-10 16:32pm
   
Yep, and Yep. And Yep. But Oh Lordy do I hate cursors. :D
On the other hand, should they ever decide to use this outside our little group of 50 people (the table has maybe...500...rows in it, I'll redo it. Thanks.
But OMG you should see the hoops I had to jump through just to get this far. Here's a bit of what the source table looks like:
BOB | 11/29/2010 12:00:00 AM | 03:30P to 04:00P
BOB | 12/7/2010 12:00:00 AM | All Day
BOB | 12/7/2010 12:00:00 AM | NOON to 04:00P

Gotta love those dates and times, eh?
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can do it in sql server , TmpOffice is original table which has Sno, OfficeName,starttime and endtime.

--Creating Tmp table from original and added a new column FinalEndtime with value 9999-01-01 00:00:00.000 as datetime

select   * ,'9999-01-01 00:00:00.000' as FinalEndTime   into TmpOffice1 from TmpOffice

--update all MAX values to MIN values in TmpOffice1

UPDATE t
SET t.FinalEndTime = mx.MaxColumn
FROM TmpOffice1 t
 cross apply
    (select max(EndTime) as MaxColumn
     from TmpOffice1 mx
     where mx.OfficeName = t.OfficeName
     group by OfficeName) mx
 cross apply
    (select min(EndTime) as MinColumn
     from TmpOffice1 mn
     where mn.OfficeName = t.OfficeName
     group by OfficeName) mn
WHERE t.EndTime = mn.MinColumn

-- Deleting the unwanted records from tmptable
Delete from TmpOffice1 where FinalEndTime='9999-01-01 00:00:00.000'


-- now below will return your expected results
select * from TmpOffice1
   
Comments
GenJerDan 29-Dec-10 9:22am
   
Thanks, but that didn't do it. It collapsed all the dates, not just the adjacent ones. For instance, if the dates were 11/20, 11/21, 11/22, and then again 12/13, it set the start as 11/20 and the end as 12/13, whe what I need is 11/20-11/22, and then 12/13 standing alone. Maybe my question wasn't clear. Sorry.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Seems I'm a little late, but this should do it (you might want to tweak it depending on your specific needs, but you should get the general idea):
-- Adjust the dates.
UPDATE Leave
SET
	FromDate =
	(
		SELECT
			MIN(FromDate)
		FROM Leave AS L
		WHERE
			L.Employee = Leave.Employee
	),
	ToDate =
	(
		SELECT
			MAX(ToDate)
		FROM Leave AS L
		WHERE
			L.Employee = Leave.Employee
	)

-- Delete the extra rows.
DELETE FROM Leave
WHERE
	NOT EventID IN
	(
		SELECT
			Min(EventID)
		FROM Leave AS L
		WHERE
			L.Employee = Leave.Employee
	)


That would probably work best if you have an index on the Employee column, though it may not matter since you're going to scan the entire table anyway.
   
Comments
GenJerDan 29-Dec-10 16:20pm
   
It does the same as Answer 1 above, takes the first date and the very last date and uses them, even though there are dates between which are not adjacent to each other. My orginal Q sucked, because it wasn't clear enough.
AspDotNetDev 29-Dec-10 16:22pm
   
Hmmm, maybe this isn't what you want. Sounds like you are trying to do some other type of collapsing of dates. Maybe if the start dates are within a certain time period. Or if the from/to dates overlap between two rows. The question is not clear.
AspDotNetDev 29-Dec-10 16:24pm
   
Haha, seems you beat me to the punch in pionting out that my answer is not correct.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Take a look at the MAX and MIN functions in SQL.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100