Linq Query to find min and max in a datatable

Question

I am fetching the attendance details of employees in a datatable . It looks like this

Day       SwipeTime
12/31/2012  11AM
12/31/2012  1PM
12/31/2012  7PM
12/31/2012  8PM
1/1/2012    2PM
1/1/2012    7PM
1/1/2012    8PM
1/2/2012    10AM
1/2/2012    8PM

I need to display the date and totalhours for an employee where totalhours = lastswipe - firstwipe

my result would look like

Day TotalHours
12/31/2012  9
1/1/2012    6
1/2/2012    12

So i need to find min and max swipes grouped by date. Please help me write the query

no field called swipetype as to whether ita a swipe in or swipe out? — naveen, Jan 09 '13 at 07:49
@naveen ..doesnt matter its just an example..i need to get min and max...@sweko : good observation..but i am not thinking about that now :) — sajad, Jan 09 '13 at 07:54

Tim Schmelter · Accepted Answer · 2013-01-09T09:14:05.487

You can use Enumerable.GroupBy to group by date. Then you could create a Dictionary<DateTime,int> where the key is the date and the value are the total-hours for that date:

Dictionary<DateTime,int> dateGroups = table.AsEnumerable()
    .GroupBy(r => r.Field<DateTime>("Date").Date)
    .Select(g => new{
        Date = g.Key, 
        TotalHours = g.Sum(r => 
               DateTime.ParseExact(r.Field<string>("SwipeTime"), 
                      "htt", CultureInfo.InvariantCulture).Hour)
    }).ToDictionary(x => x.Date, x => x.TotalHours);

Edit: So, that was the TotalHours of the whole day, now the desired max-min calculation. You also have changed your desired timespan-format to "11:41 AM". Then i would use DateTime.Parse(str).TimeOfDay to get the timespan.

Dictionary<DateTime, int> dateGroups = table.AsEnumerable()
.GroupBy(r => r.Field<DateTime>("Date").Date)
.Select(g => new
{
    Date = g.Key,
    TotalHours = 
       (g.Max(r => DateTime.Parse(r.Field<string>("SwipeTime")).TimeOfDay)
      - g.Min(r => DateTime.Parse(r.Field<string>("SwipeTime")).TimeOfDay)).Hours
}).ToDictionary(x => x.Date, x => x.TotalHours);

I am getting this error :String was not recognized as a valid DateTime. Apparently my swipe format is like 11:41 PM....what should be the string format in this case? — sajad, Jan 09 '13 at 08:10
@sajad: Edited my answer to take your max-min reuirement into account and your new date/timespan format. — Tim Schmelter, Jan 09 '13 at 08:18

Sergey Berezovskiy · Answer 2 · 2013-01-09T08:38:55.737

In this answer ordered list of times for day is created to avoid two things - parsing all rows twice, and creating two sets from parsed values to get max and min item. Also I do not parse day before grouping, because same date will have same string value.

var query = from row in table.AsEnumerable()
            group row by row.Field<string>("Day") into g
            let times = g.Select(r => DateTime.Parse(r.Field<string>("SwipeTime")))
                         .OrderBy(t => t.TimeOfDay)
                         .ToList()
            select new
            {
                DateTime.Parse(g.Key).Date,
                (times.Last() - times.First()).TotalHours
            };

Result is a collection of anonymous objects with two properties DateTime Date and double TotalHours

You don't need `DateTime.ParseExact`, you can simply use `DateTime.Parse("11:41 AM")`. — Tim Schmelter, Jan 09 '13 at 08:28

Linq Query to find min and max in a datatable

2 Answers2