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 = '03-20-2023';
SELECT e.EmpID, a.JobCode
FROM Employee e
JOIN (
SELECT
EmpID,
COALESCE(JobCode,'BEFORE') as JobCode,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated ASC) AS mdr
FROM [JobCode]
WHERE Updated <= @ActDate
) a ON a.EmpID = e.EmpID
WHERE a.mdr = 1
| EmpID | JobCode |
|---|---|
| E1 | JC1 |
| E2 | JC0 |
DECLARE @ActDate Date = '03-20-2023';
SELECT
EmpID,
COALESCE(JobCode,'BEFORE') as JobCode,
Updated,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated ASC) AS mdr
FROM [JobCode]
WHERE Updated <= @ActDate
| EmpID | JobCode | Updated | mdr |
|---|---|---|---|
| E1 | JC1 | 2023-01-01 08:30:00.000 | 1 |
| E1 | JC2 | 2023-03-13 04:15:00.000 | 2 |
| E2 | JC0 | 2022-12-01 10:15:00.000 | 1 |
| E2 | JC1 | 2023-02-01 10:15:00.000 | 2 |
DECLARE @ActDate Date = '03-20-2023';
SELECT e.EmpID, a.JobCode
FROM Employee e
JOIN (
SELECT
EmpID,
COALESCE(JobCode,'BEFORE') as JobCode,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated DESC) AS mdr
FROM [JobCode]
WHERE Updated <= @ActDate
) a ON a.EmpID = e.EmpID
WHERE a.mdr = 1
| EmpID | JobCode |
|---|---|
| E1 | JC2 |
| E2 | JC1 |
DECLARE @ActDate Date = '03-20-2023';
SELECT
EmpID,
COALESCE(JobCode,'BEFORE') as JobCode,
Updated,
ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated DESC) AS mdr
FROM [JobCode]
WHERE Updated <= @ActDate
| EmpID | JobCode | Updated | mdr |
|---|---|---|---|
| E1 | JC2 | 2023-03-13 04:15:00.000 | 1 |
| E1 | JC1 | 2023-01-01 08:30:00.000 | 2 |
| E2 | JC1 | 2023-02-01 10:15:00.000 | 1 |
| E2 | JC0 | 2022-12-01 10:15:00.000 | 2 |