I have a list of employees that have multiple IDs for the same employee. I need to pick the ID that has the closest matching prior date to a valid date in another table. In the query below, I'd like to end up updating the MatchedID bit field to 1 for EmpID 2 for RecordID 555 because the GoodDate value is the closest to the MatchDate value, and EmpID 9 for RecordID 333 because the BadDate is the closest to the MatchValue.
But I'm having a problem writing the SQL to compare dates in multiple columns in multiple rows
DECLARE @Employees TABLE (ID int identity(1,1),RecordID int,EmpID int, FirstName varchar(50),LastName varchar(50), GoodDate date, BadDate date)
INSERT INTO @Employees(RecordID,EmpID, FirstName, LastName, GoodDate, BadDate)
VALUES
(555,1,'John','Jones','20170102','20161220'),
(555,2,'John','Jones','20170602','20170531'),
(555,3,'John','Jones','20170531','20170615'),
(333,8,'Sue','Smith','20170601','20170602'),
(333,9,'Sue','Smith','20170613','20170610')
DECLARE @Records TABLE(ID int identity(1,1), RecordID int, MatchDate date)
INSERT INTO @Records(RecordID, MatchDate)
VALUES(555, '20170603'),(333,'20170611')
select *
from @Employees E INNER JOIN @Records R ON E.RecordID = R.RecordID