SQL - Updating Employee Title based on most Recent Position

Question

I have 2 tables:

Table1

    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')

Which 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')

Which 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 do I update table1 employee title based on most recent position in table 2?

The below code doesn't get the most recent date, instead I get duplicates of employees from Table2 with the same datepositionstart.

 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 title 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)

The assumption I made is that table2.EMPLOYEEnumber is a foreign key which references table1.EMPLOYEEID.

The expected data is not achieved, because the initial insert into 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, as people can and do 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 this, Hope this 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