how to update one table based on other table records

Question

1.00/5 (1 vote)

See more:

i have following tables
1.EmployeeCategory

SQL

Id    Employee_Id   Sector_Id   Cat_Id   Date
1       sa            1            2      mmmmm

2nd table
2.DailyPerformance

SQL

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

C#

 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);
                   
                }

            }

i want to check last 10 records against each employee,then base on query update the category in the Employeecategory table

Posted 9-Apr-15 22:26pm

Sajid227

Updated 10-Apr-15 9:44am

v3

Add a Solution

Comments

Member 11312264 10-Apr-15 4:46am

use insert trigger in daily performance table

ZurdoDev 10-Apr-15 11:46am

1. It is not clear what you want.
2. Where are you stuck? Just write an update statement.

John C Rayan 10-Apr-15 15:13pm

It is a question of at what point you want to update. While someone update or if you have already lot of records and you want to update with a SQL.

Sajid227 10-Apr-15 15:46pm

i want to update only category in the employee category table base on daily performance record

Maciej Los 10-Apr-15 17:17pm

Still unclear. Please, provide expected output (result).

Sajid227 10-Apr-15 17:25pm

EmployeeCategory tbl before update
id Emp_ID Sec_Id Cat_Id
1 111 1 2
if last ten records of that employee in DailyPerformance shows that his Cat_Id 'A' is more than 4 then EmployeeCategory table looks ;like
id Emp_ID Sec_Id Cat_Id
1 111 1 1
else remains unchanged

Maciej Los 10-Apr-15 17:56pm

Oh My Gosh... Above "data" does not help to understand your issue.

Sajid227 10-Apr-15 17:58pm

plz check this post i explained every thing in it hope you will get point

how to update sql table using other table

John C Rayan 10-Apr-15 18:43pm

You want to update from c# using Linq? What issue are you having.

Sajid227 11-Apr-15 1:21am

record is not updating in EmployeeCategory table//do you suggest me any other solution??

John C Rayan 11-Apr-15 5:05am

You will never get updated for categoryid = 1. Are you getting updated for categoryid = 2. Because your 1st if condition is always overridden by 2nd if condition.

Sajid227 11-Apr-15 13:44pm

how to tackle this

Sajid227 11-Apr-15 13:45pm

how to handle this

Sajid227 11-Apr-15 13:45pm

how to handle this

John C Rayan 12-Apr-15 5:00am

Reverse your If statements order. Start with small counts. For example in your case first If should be if(emp1.Count()>=3) and then if(emp1.Count()>=4). This way it would as expected.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-04-11T22:53:00

As i mentioned in the comment to the question, your requirements are not clear. So, i can only suggest you sample solution.

An update statement might look like:

SQL

UPDATE t1 SET t1.Field1 = t2.Field2
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.PK = t2.FK

where:
PK - Primary Key
FK = Foreign Key

Primary and Foreign Key Constraints[^]

Change the code to your needs.

Next time, please be more specific and provide more details to be able to understand criteria to change data.

Thava Rajan · Answer 2 · 2015-04-13T02:16:00

Solution 2

check this example[^] is it ok

Posted 13-Apr-15 2:16am

Thava Rajan

Sajid227 · Answer 3 · 2015-04-15T23:02:00

C#

var EmpCat = _service.GetDailyPerformanceBySector_Id(sectorId);
            //var Empcategory = _service.GetEmployeeCategoryBySector_Id(sectorId).FirstOrDefault();
            //   double datediff =( date - Empcategory.Date).TotalDays;
            //   if (datediff >= 5)
            //   {
                   foreach (var empcat in EmpCat)
                   {
                       var emp1 = EmpCat.Where(x => x.Employee_Id == empcat.Employee_Id).OrderByDescending(x => x.Date).Take(5).ToList();
                       int category = 0;
                       foreach (var emp in emp1)
                       {
                           category += emp.Category_Id;

                       }
                       decimal avg = category / (decimal)emp1.Count;
                       CTP.HRMS.Business.EmployeeCategory empCategory = _service.GetEmployeeCategoryBySector_Id(sectorId).LastOrDefault(x => x.Employee_Id == empcat.Employee_Id);
                       if (avg < 2)
                       {
                           if (empCategory.Category_Id <= 2)
                           {
                               empCategory.Category_Id = 1;
                               empCategory.Date = DateTime.Today;
                           }
                           else if (empCategory.Category_Id <= 3)
                           {
                               empCategory.Category_Id = 2;
                               empCategory.Date = DateTime.Today;
                           }
                       }
                       if (avg > 2)
                       {
                           if (empCategory.Category_Id == 1)
                           {
                               empCategory.Category_Id = 2;
                               empCategory.Date = DateTime.Today;
                           }
                           else if (empCategory.Category_Id == 2)
                           {
                               empCategory.Category_Id = 3;
                               empCategory.Date = DateTime.Today;
                           }
                       }
                       _service.UpdateEmployeeCategory(empCategory);
                   }

that my code that fullfill my requirement,thanx to all to make me think right in to my problem and then to solution

how to update one table based on other table records

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0