Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calculate duration between first login and last logout from a set of records in datatable

I am reading an Excel document using ADO.Net into a Dataset. Dataset Contains set of employee records which contains login and logout time as a Datatable. I need to fetch the employees's first login and last logout time as final record from the set of records based on employee id and date. Here is my example data:

    Emp Id  Name            Login             Logout
    12345   RAMACHANDRAN    7/30/2013 8:40  7/30/2013 10:40
    12345   RAMACHANDRAN    7/30/2013 12:30 7/30/2013 14:20
    12345   RAMACHANDRAN    8/01/2013 18:10 8/01/2013 20:20
    12345   RAMACHANDRAN    8/01/2013 20:40 8/01/2013 22:00
    12346   RAVI            8/03/2013 12:30 8/03/2013 14:20
    12346   RAVI            8/03/2013 18:10 8/03/2013 20:20

I need the final record with calculated duration as

 Emp Id    Name          Login           Logout            Duration
 12345   RAMACHANDRAN   7/30/2013 8:40  7/30/2013 14:20    5:40
 12345   RAMACHANDRAN   8/01/2013 18:10 8/01/2013 22:00    3:50
 12346   RAVI           8/03/2013 12:30 8/03/2013 20:20    7:50

Is it the best way to use Linq for querying this dataset? Which way is the best

like image 749
satyanarayana Avatar asked Sep 12 '13 07:09

satyanarayana


1 Answers

Something like this:

dt.GroupBy(l => new {l.EmpId, l.Login.Date},
    (key, g)=> new {
             EmpId = key.EmpId,
             Name = g.First().Name,
             Login = g.Min(d => d.Login),
             Logout = g.Max(d => d.Logout),
             Duration = g.Min(d => d.Login) - g.Max(d => d.Logout)
     });
like image 79
Hamlet Hakobyan Avatar answered Sep 27 '22 22:09

Hamlet Hakobyan