SQL - update employee header based on latest position

Question

I have 2 tables:

Table 1

CREATE TABLE #Table1( EMPLOYEEID [nvarchar] (100) NULL, Name [nvarchar] (100) NULL, TITLE [nvarchar] (100) NULL) INSERT INTO #Table1 (EMPLOYEEID, Name, TITLE) VALUES ('1','Tom','System Admin'), ('2','Bill','Server Admin'), ('3','John','Senior Server Admin'), ('4', 'Bob', 'IT Staff') , ('5', 'Jill', 'Senior IT Staff') 

What gives me:

 select * from #Table1 +------------+-------+---------------------+ | EMPLOYEEID | Name | Title | +--------------------+---------------------+ | 1 | Tom | System Admin | | 2 | Bill | Server Admin | | 3 | John | Senior Server Admin | | 4 | Bob | IT Staff | | 5 | Jill | Senior IT Staff | +--------------------+---------------------+ 

table 2

  CREATE TABLE #Table2( EMPLOYEEnumber [nvarchar] (100) NULL, Name [nvarchar] (100) NULL, positiontitle [nvarchar] (100) NULL, datepositionstart [date] NOT NULL) INSERT INTO #Table2 (EMPLOYEEnumber, Name, positiontitle, datepositionstart) VALUES ('1','Tom','System Admin', '2014-07-03'), ('1','Tom','Server Admin', '2014-08-13'), ('1','Tom','Senior Server Admin', '2016-07-23'), ('2', 'Bob', 'IT Staff', '2014-07-03') , ('2', 'Bob', 'Senior IT Staff', '2016-10-15') 

What gives me:

 select * from #Table2 +----------------+-------+---------------------+-----------------+ | EMPLOYEEnumber | Name | positiontitle |datepositionstart| +----------------+-------+--------------------+-----------------+ | 1 | Tom | System Admin | 2014-07-03 | | 2 | Tom | Server Admin | 2014-08-13 | | 3 | Tom | Senior Server Admin | 2016-07-23 | | 4 | Bob | IT Staff | 2014-07-03 | | 4 | Bob | Senior IT Staff | 2016-10-15 | +------------------------+---------------------+-----------------+ 

How to update the employee header table1 based on the last position in table 2?

The code below does not get the most recent date, instead I get duplicate employees from Table 2 with the same dateposition parameter.

  UPDATE Table1 SET E.TITLE = hre.[PositionTitle] FROM Table1 E JOIN Table2 hre on hre.EmployeeNumber = E.EMPLOYEEID WHERE E.Name = hre.Name AND datepositionstart = (SELECT MAX(datepositionstart) FROM table2 hre WHERE hre.EmployeeNumber = E.EMPLOYEEID) 

Expected results:

 +------------+-------+---------------------+ | EMPLOYEEID | Name | Title | +--------------------+---------------------+ | 1 | Tom | Senior Server Admin | | 2 | Bill | Server Admin | | 3 | John | Senior Server Admin | | 4 | Bob | Senior IT Staff | | 5 | Jill | Senior IT Staff | +--------------------+---------------------+ 

Answer 1

Use row_number() to determine the most recent heading for each employee:

 update set title = t2.positiontitle from table1 t1 join (select t2.*, row_number() over (partition by employeenumber order by datepositionstart desc) as seqnum from table2 t2 ) t2 on t1.employeeid = t2.employeenumber and seqnum = 1; 

Answer 2

 UPDATE e SET E.TITLE = t.[PositionTitle] FROM #Table1 e JOIN #Table2 t ON t.EMPLOYEEnumber= e.EMPLOYEEID WHERE t.datepositionstart = (SELECT MAX(datepositionstart) FROM #table2 hre WHERE hre.EmployeeNumber = E.EMPLOYEEID GROUP BY hre.EMPLOYEEnumber) 

My assumption is that table2.EMPLOYEEnumber is a foreign key that references table1.EMPLOYEEID .

The expected data is not achieved, because the original insert in table2 creates the following:

 EMPLOYEEnumber Name positiontitle datepositionstart 1 Tom System Admin 2014-07-03 1 Tom Server Admin 2014-08-13 1 Tom Senior Server Admin 2016-07-23 2 Bob IT Staff 2014-07-03 2 Bob Senior IT Staff 2016-10-15 

I would not use names as a means of joining tables, especially history tables, since people can and can change their names over time.

Answer 3

Try something like this.

 Update T1 Set T1.Title = T2.PositionTitle FROM Table1 T1 JOIN ( SELECT EmployeeNumber,PositionTitle, Max(datepositionstart) AS datepositionstart FROM Table2 group by EmployeeNumber,PositionTitle ) T2 on T1.EMPLOYEEID = T2.EmployeeNumber 

Answer 4

Try it, hope it helps you.

  Update T1 Set T1.Title = d.PositionTitle FROM #Table1 T1 JOIN ( select EMPLOYEEnumber,positiontitle,datepositionstart ,ROW_NUMBER() over (PARTITION BY EMPLOYEEnumber order by datepositionstart desc) rn from #Table2 ) d on T1.EMPLOYEEID = d.EmployeeNumber and d.rn = 1