none
DataTable issue RRS feed

  • Question

  • Hi All,
    I have a datatable with some data :

    ActivityNr ActivityStartTime ActivityDuration
    001 20:30 00:30
    002 20:30 00:40
    003 20:40 00:30
    004 20:50 00:30
    005 20:55 00:30

    As you can see, Activity 001 and 002 have the same StartTime(20:30), bud activity 002 has a longer Duration(40 minutes) then Activity 001(30 minutes).
    Now i have to check all activities StartTimes and when they have the same StartTime(like 001 and 002), then i have to check the 'ActivtityDuration' of those Activities and delete the Activity with shorter duration from the datatable.
    Assuming the datatable above, the end resulat most be:

    ActivityNr ActivityStartTime ActivityDuration
    002 20:30 00:40
    003 20:40 00:30
    004 20:50 00:30
    005 20:55 00:30

    001 is deleted because it had the same starttime as 002, bud a shorter duration.
    Can anyone help me with this issue with some code ?

    Thanx.

     

    Monday, June 9, 2008 8:49 PM

Answers

  • The solution depends on your scenario.

     

    Solution with the DataTable

    If you want to do this programmatically in .Net on client side, than run the following algorithm:

     

    1. Create a dictionary that maps DateTime to TimeStamp

    this table will hold the maximum ActivityDuration value for each ActivityStartTime

     

    2. for each row in your table:

    2.1 read the value of ActivityStartTime column as startTime

    2.2 read the value of ActivityDuration column as duration

    2.3.1 if the dictionary does not contain a value for startTime, add (startTime, duration) to it

    2.3.3 otherwise, check whether the previous value is less then the current duration, update it

     

    3. Now you have a table with max durations for each value. Run again on the same table, and mark rows that do not have the maximum duration as Deleted.

     

    Solution with the TSQL

    But, if you want to update the database on SQL Server, you do not actually need to read it into DataTable in order to perform this task.

     

    You can do the same with this query:

     

    DELETE

    FROM [ActivityDatabase]

    FROM [ActivityDatabase] AS [OuterActivityDatabase]

    WHERE [ActivityDuration] < (SELECT max([ActivityDuration]) FROM [ActivityDatabase] WHERE [ActivityStartTime] = [OuterActivityDatabase].[ActivityStartTime])

     

    Note that FROM keyword should appear twice in the DELETE clause, the second one is needed to specify an alias for it.

    Tuesday, June 10, 2008 3:11 AM
    Moderator