CODEWITHSUNDEEP
sqlsql-servert-sql
Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

INSERT INTO based on SELECT and versioned record

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

Upvotes: 1

Views: 96

Answers (3)

Akash Patel
Akash Patel

Reputation: 339

;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

Upvotes: 2

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14218

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

enter image description here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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 ;

Upvotes: 2

Related Questions