how to update one table based on other table records
sorry 1st for asking same question again coz that was not clear
my requirement are followings
1.there are two same table in sql.
2.Table DailyPerformance is used to record daily performance of every Employee
like A for good,B for normal c FOR poor,
3.after ten days i want that system automatically check against each employee his performance by counting A,B,C and then on this bases update the EmployeeCategory TABLE.
Sample Data is follow
1.EmployeeCategory
Id Employee_Id Sector_Id Cat_Id Date
1 sa 1 2 mmmmm
2nd table
2.DailyPerformance
Id Employee_Id Sector_Id Cat_Id Date
1 sa 1 2 21/01/15
2 sa 1 1 22/01/15
3 sa 1 3 23/01/15
4 sa 1 1 24/01/15
5 sa 1 1 25/01/15
6 sa 1 1 26/01/15
7 sa 1 1 27/01/15
i want that if employee cat_id==1 is more than 4 then it will update the cat_id in EmployeeCategory table of that Employee_Id
i am using following code
Entities entities = new Entities();
var EmpCat = _service.GetAllDailyPerformance();
foreach (var empcat in EmpCat)
{
var emp1 = from dp in entities.DailyPerformances.OrderByDescending(x=>x.Date).Where(x=>x.Employee_Id==empcat.Employee_Id && x.Category_Id==1).Take(10) select dp; if(emp1.Count()>=4) { var dpemp = empcat; CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory(); emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 1; _service.UpdateEmployeeCategory(emp); }
if(emp1.Count()>=3)
{ var dpemp = empcat;
CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory(); emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 2; _service.UpdateEmployeeCategory(emp); }