i have sql table like
[Id] ,[Employee_Id] ,[Rank_Id]
,[EmployeeStatusType_Id] ,[Sector_Id] ,[DutyType_Id]
,[EmployeeShift_Id] ,[StartDate] ,[EndDate]
and i am getting record using record like this
var sectorEmployees = _service.GetEmployeeStatusBySector_Id(sectorId).OrderBy(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).OrderBy(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();
var absentDetail = sectorEmployees.Where(x=>x.EmployeeStatusType_Id == 2).OrderBy(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).OrderBy(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); }
var longLeaves = sectorEmployees.Where(x => x.EmployeeStatusType_Id == 4).OrderBy(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList(); foreach (var longleave in longLeaves) { Employee emp = _service.GetEmployee(longleave.Employee_Id); dataset.LongLeaveDetail.Rows.Add(emp.Name, emp.Id, longleave.StartDate); } here i need to get latest record against all employees that are in that sector,then depending on their status i have show then on duty or absent or on leave,for onjob empstatus is 1,absent 2,and for leave is 3.
my problem is that one employee is going to these status at once,my requirement is that one employee must be at at one status at time.how can i do it?