How to use Linq To Sql to count the idle working duration in a day?

Question

For example, I have a Task MIS, the data is:

Id User Task TaskStartTime TaskFinishTime

1 Mike task1 2009-07-28 09:00:00 2009-07-28 09:45:00

2 Mike task2 2009-07-28 09:30:00 2009-07-28 09:40:00

3 Mike task3 2009-07-28 09:50:00 2009-07-28 10:30:00

4 Mike task4 2009-07-28 10:20:00 2009-07-28 10:30:00

5 Mike task5 2009-07-28 10:40:00 2009-07-28 11:50:00

How can I use Linq To Sql to cout the idle Time Duration for a day such as 2009-07-28?

The result should be:

5 minutes (between task1, task2 and task3) + 10 minutes (between task4 and task5)

= 15 minutes

Answer 1

Something like this

Suppose you have a collection of task objects:

public class Task
{
    public Guid ID { get; set; }
    public string User { get; set; }
    public string Task { get; set; }
    public DateTime TaskStartTime { get; set; }
    public DateTime TaskFinishTime { get; set; }
}

var tasks = new List<Task> ();
// Fill tasks here...

var idleTimeInMinutes =
    tasks.Last ().TaskFinishTime.Subtract (tasks.First ().TaskStartTime).Minutes
    - tasks.Sum (t => t.TaskFinishTime.Subtract (t.TaskStartTime).Minutes);

EDIT: Now, wait, I see tasks overlap in your example. Then what I wrote is not going to work. I'll have to think about it...

Answer 2

Alright, I've worked out an option that will work.

you can use this:

from a in Tasks
join b in Tasks on true equals true
where a.TaskStartTime < b.TaskFinishTime
where a.TaskFinishTime > b.TaskStartTime
where a.Id < b.Id
select new {
    Id = a.Id,
    User = a.User,
    Task = (a.Task + " + " + b.Task),
    TaskStartTime = (a.TaskStartTime < b.TaskStartTime ? a.TaskStartTime : b.TaskStartTime),
    TaskFinishTime = (a.TaskFinishTime > b.TaskFinishTime ? a.TaskFinishTime : b.TaskFinishTime)
 }

To Collapse Down Individual tasks, until there are no intersections, then just take the sum of All Time, minus the sum of time in all tasks.

Answer 3

Pull the interesting records into memory and process them there (by collapsing overlapping ranges into single ranges). The collapsing task is not suitable to querying approaches, such as sql or linqtosql.

Answer 4

Here's some code that will do the work, using Linq. I don't know if it's in the form you were looking for, but the logic works with the data you gave us. You ought to be able to rearrange it into the proper shape for your app.

MODIFIED

var rows = new[] { 
    new { Id =  1, Name="Mike", TaskName="task1", StartTime=DateTime.Parse("2009-07-28 09:00:00"), EndTime=DateTime.Parse("2009-07-28 09:45:00") },
    new { Id =  2, Name="Mike", TaskName="task2", StartTime=DateTime.Parse("2009-07-28 09:30:00"), EndTime=DateTime.Parse("2009-07-28 09:40:00") },
    new { Id =  3, Name="Mike", TaskName="task3", StartTime=DateTime.Parse("2009-07-28 09:50:00"), EndTime=DateTime.Parse("2009-07-28 10:30:00") },
    new { Id =  4, Name="Mike", TaskName="task4", StartTime=DateTime.Parse("2009-07-28 10:20:00"), EndTime=DateTime.Parse("2009-07-28 10:30:00") },
    new { Id =  5, Name="Mike", TaskName="task5", StartTime=DateTime.Parse("2009-07-28 10:40:00"), EndTime=DateTime.Parse("2009-07-28 11:50:00") },
        };

var ranges = rows.Select(x => new { StartTime = x.StartTime, EndTime = x.EndTime });
int rangeCount = 0;
while (ranges.Count() != rangeCount)
{
    rangeCount = ranges.Count();
    ranges = ranges.Select(x => new {
        StartTime = ranges.Where(y => (y.StartTime <= x.StartTime && y.EndTime >= x.StartTime) || (y.StartTime <= x.EndTime && y.EndTime >= x.EndTime)).Min(y => y.StartTime),
        EndTime = ranges.Where(y => (y.StartTime <= x.StartTime && y.EndTime >= x.StartTime) || (y.StartTime <= x.EndTime && y.EndTime >= x.EndTime)).Max(y => y.EndTime),
    }).Distinct().ToList();  // ToList required to avoid recursion issues
}

TimeSpan gapTime = ranges.Max(x => x.EndTime) - ranges.Min(x => x.StartTime) - new TimeSpan(ranges.Sum(x => (x.EndTime - x.StartTime).Ticks));

System.Diagnostics.Trace.WriteLine(gapTime);