i have a table in which i am storing multiple record of same employee and there are multiple employees also,i want to get list last record of all employees,then base on their status divide it like on job empstatus_Id 1,absent empstatus_Id 2, leave empstatus_Id 3,i want to get list of all employees latest record.
[Id]
,[Employee_Id]
,[Rank_Id]
,[EmployeeStatusType_Id]
,[Sector_Id]
,[DutyType_Id]
,[EmployeeShift_Id]
,[StartDate]
,[EndDate]
and code behind is
var sectorEmployees = _service.GetEmployeeStatusBySector_Id(sectorId).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();
var employeesbeat = sectorEmployees.Where(x => x.DutyType_Id == 4 && x.EmployeeStatusType_Id == 1).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();
var absentDetail = sectorEmployees.Where(x => x.EmployeeStatusType_Id == 2).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();
foreach (var absent in absentDetail)
{
Employee emp = _service.GetEmployee(absent.Employee_Id);
dataset.AbsentDetail.Rows.Add(emp.Name, emp.Id, absent.StartDate);
}
var shortleave = sectorEmployees.Where(x => x.EmployeeStatusType_Id == 3).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();
foreach (var shrtleave in shortleave)
{
Employee emp = _service.GetEmployee(shrtleave.Employee_Id);
dataset.ShortLeaveDetail.Rows.Add(emp.Name, emp.Id, shrtleave.StartDate);
}
The problem is that one employee is repeated in more than one status, but my requirement is as one employee last status is falling only in one status then its must goes only in one??hope u got??
My table structure and data:
emp_Id status_Id startdate EndDate
227 1 2015-1-23 2015-2-02
227 2 2015-2-2 2015-2-03
227 3 2015-2-2 2015-2-04
I need to check the last status and put employee into that system.