INSERT INTO based on SELECT and versioned record

Question

I have a table that contains the current state of employees like below :

 SELECT EmployeeId ,OfficeId from EmpCurrent

The output is like below :

EmployeeId OfficeId
54          67
64          57
89          23

And I have another table containing the full history for an employee except for OfficeId which isn't historised (I added the column OfficeId which is NULL to insert from EmpCurrent) :

SELECT EmployeeId ,Func AS [Function],OfficeId,Version from EmpHistory

The output is like below for EmployeeId=54:

EmployeeId Function OfficeId Version
54         Manager  NULL     1
54         Director NULL     2
54         HeadOf   NULL     3

I want to get the OfficeId from EmpCurrent and insert it in EmpHistory in the last Version(max version) of the each Employee, my output will be like below :

EmployeeId Function OfficeId Version
54         Manager  NULL     1
54         Director NULL     2
54         HeadOf   67       3

What have you tried so far, and where are you getting stuck with it? This is a pretty standard use case for either a [correlated subquery](https://stackoverflow.com/a/17269406/1324345) or a windowing function. — alroc, Feb 19 '20 at 13:24

score 2 · Answer 1 · answered Feb 19 '20 at 13:27

If you want the most recent value for the join, then:

select eh.*, ec.officeid
from (select eh.*,
             row_number() over (partition by employeeid order by version desc) as seqnum
      from emphistory eh
     ) eh left join
     empcurrent ec
     on ec.employeeid = eh.employeeid and eh.seqnum = 1;

This is one of the unusual instances where you filter on the first table in a left join. You can also do this as:

select eh.*,
       (case when eh.seqnum = 1 then ec.officeid end) as officeid
from (select eh.*,
             row_number() over (partition by employeeid order by version desc) as seqnum
      from emphistory eh
     ) eh left join
     empcurrent ec
     on ec.employeeid = eh.employeeid ;

Nguyễn Văn Phong · Accepted Answer · 2020-02-19T13:40:59.363

demo on db<>fiddle

Firstly, You need to get 3 fields h.EmployeeId, c.OfficeId, max(Version) as MaxVersion.

Secondly, Update h.OfficeId = cte.OfficeId on #EmpHistory table with 2 conditions h.EmployeeId = cte.EmployeeId and MaxVersion = h.Version

Full Code

;with cte as( 
select h.EmployeeId, c.OfficeId, max(Version) as MaxVersion
from #EmpHistory h
inner join #EmpCurrent c on h.EmployeeId = c.EmployeeId
group by h.EmployeeId, c.OfficeId
)

update h
set h.OfficeId = cte.OfficeId
from #EmpHistory h
inner join cte on h.EmployeeId = cte.EmployeeId and MaxVersion = h.Version

Output

score 2 · Answer 3 · answered Feb 19 '20 at 13:43

;WITH CTE_EMP
AS
(
    SELECT 
        EH.EmployeeId
        ,Version        = MAX(Version)
    FROM
        EmpHistory AS EH
    GROUP BY
        EH.EmployeeId
)
select 
     EH.EmployeeId
    ,EH.Func
    ,OfficeId       = CASE WHEN CTE.Version = EH.Version THEN EC.OfficeId END
    ,EH.Version
from 
    EmpHistory AS EH 
    LEFT OUTER JOIN EmpCurrent AS EC ON EC.EmployeeId = EH.EmployeeId
    LEFT OUTER JOIN CTE_EMP AS CTE ON CTE.EmployeeId = EH.EmployeeId

INSERT INTO based on SELECT and versioned record

3 Answers3