Have 2 tables - tblEmployee which is our main employee table that's imported from an HR system and a table thats been created called tblEmployeeDeptByDay. The data is as -

DROP TABLE IF EXISTS #tblEmployee
DROP TABLE IF EXISTS #tblEmployeeDeptByDay
--tblEmployee
SELECT '123456' AS ID
        , 'John Smith' AS EmpName
        , 99999 AS DeptID --UPDATED TODAY TO A NEW DEPT ID
        , DATEADD(D, DATEDIFF(d, 0, getdate()), 0) AS StartDate
        , 'blah@blah.com' Email
        , 'X4' ShiftPattern
        , 'Tom Jones' AS Manager
        , 'W33321' AS Ext
INTO #tblEmployee
--tblEmployeeByDay
SELECT * 
INTO #tblEmployeeDeptByDay
FROM (
SELECT 
     '123456' AS ID
    , 548758 AS DeptID
    , DATEADD(YY, DATEDIFF(YY,0,GETDATE()), 0) AS StartDate
    , DATEADD(D, DATEDIFF(d, 0, getdate())-1, 0) AS EndDate
UNION ALL
SELECT 
     '123456' AS ID
    , 548758 AS DeptID
    , DATEADD(D, DATEDIFF(d, 0, getdate()), 0) AS StartDate
    , NULL AS EndDate
    ) AS a

SELECT * FROM #tblEmployee

SELECT * FROM #tblEmployeeDeptByDay

When HR moves an employee to another department currently I manually update tblEmployeeDeptByDay with the end date of the old department - and the details of the new (based off of an alert I generate). I want to automate this, inserting the new record is easy but I cant work how to update the previous record with an end date.

So the final output would be as the #tblEmployeeDeptByDay data above after HR has changed the date in #tblEmployee.

I've attempted various CTE's configuration and using LEAD but cant get it to do the final update.

Output expected as above