By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Employee
([EmpID] varchar(2), [Name] varchar(3))
;
INSERT INTO Employee
([EmpID], [Name])
VALUES
('E1', 'Bob'),
('E2', 'Jim')
;
CREATE TABLE JobCode
([EmpID] varchar(2), [JobCode] varchar(3), [Updated] datetime)
;
INSERT INTO JobCode
([EmpID], [JobCode], [Updated])
VALUES
('E1', 'JC1', '2023-01-01 8:30:000.00'),
('E1', 'JC2', '2023-03-13 4:15:000.00'),
('E1', 'JC3', '2023-05-01 1:40:000.00'),
('E1', 'JC4', '2023-08-02 12:00:000.00'),
('E2', 'JC0', '2022-12-01 10:15:000.00'),
('E2', 'JC1', '2023-02-01 10:15:000.00'),
('E2', 'JC3', '2023-06-08 2:15:000.00')
;
9 rows affected
DECLARE @ActDate Date = '12-10-2022';
WITH JobCode1 AS
(
SELECT j1.EmpID, j1.JobCode
FROM (SELECT EmpID, FIRST_VALUE(JobCode) OVER (PARTITION BY EmpID ORDER BY Updated ASC) Jobcode
FROM [JobCode]) j1
GROUP BY j1.EmpID, j1.JobCode ),
JobCode2 as (
SELECT EmpID,
JobCode
FROM
(
SELECT
EmpID,
JobCode,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated ASC) AS mdr
FROM [JobCode]
WHERE Updated <= @ActDate) j2
WHERE mdr = 1)
SELECT e.EmpID, jc.JobCode
FROM Employee e
JOIN (SELECT COALESCE(j2.EmpID, j1.EmpID) EmpID, COALESCE(j2.JobCode, j1.JobCode) JobCode FROM JobCode1 j1 LEFT JOIN JobCode2 j2 ON j2.EmpID = j1.EmpID) jc ON e.EmpID = jc.EmpID
| EmpID | JobCode |
|---|---|
| E1 | JC1 |
| E2 | JC0 |