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)
DECLARE @Records TABLE(ID int identity(1,1), RecordID int, MatchDate date)
INSERT INTO @Records(RecordID, MatchDate)
from @Employees E INNER JOIN @Records R ON E.RecordID = R.RecordID