Closest matching prior date in multiple columns

1

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

2

I have to assume this is a trick question... Your data doesn't have a "MatchedID" in either table. Nothing to update.

3

That said, here's one way (there are several) to get the "latest of a group that came before..."

SELECT 
	ex.ID, ex.RecordID, ex.EmpID, ex.FirstName, ex.LastName, ex.GoodDate, ex.BadDate
FROM
	@Records r
	CROSS APPLY (
				SELECT TOP 1
					e.ID, e.RecordID, e.EmpID, e.FirstName, e.LastName, e.GoodDate, e.BadDate
				FROM
					@Employees e
				WHERE 
					r.RecordID = e.RecordID
					AND r.MatchDate > e.GoodDate
				ORDER BY 
					e.GoodDate DESC
				) ex;
4

My fault, @Employees table should be declared as:
DECLARE @Employees TABLE (ID int identity(1,1),RecordID int,EmpID int, FirstName varchar(50),LastName varchar(50), GoodDate date, BadDate date, MatchedID bit)

5

This is only using the e.GoodDate so it works for Record 555, but picks the wrong EmpID for record 333 because the BadDate is the closest and that is the record that I want to grab, not the one with the closest GoodDate.

I need to pick the record that has the closest prior date to the MatchDate looking in either column, whichever has the MAX closest to the MatchDate, doesn't matter which, just need to evaluate both.

6

Try this...

UPDATE e SET 
	e.MatchedID = 1
FROM
	@Employees e
	JOIN (
		SELECT 
			ex.ID
		FROM
			@Records r
			CROSS APPLY (
						SELECT TOP 1
							e.ID
						FROM
							@Employees e
						WHERE 
							r.RecordID = e.RecordID
							AND r.MatchDate > e.GoodDate
						ORDER BY 
							e.GoodDate DESC
						) ex
		) x
		ON e.ID = x.ID;

SELECT * FROM @Employees e;
7

That second one looks like it's only looking at the GoodDate which could be before or after (or =) to the BadDate. I ended up with a multi-tiered CTE that works. It's probably one or two steps longer than necessary, but I think it's pretty readable at least:

--SET UP TABLES AND POPULATE
DECLARE @Employees TABLE (ID int identity(1,1),RecordID int,EmpID int, FirstName varchar(50),LastName varchar(50), GoodDate date, BadDate date, MatchedID bit)
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')

--CTE CONTAINS THE MAX GOOD DATE THAT IS <= THE MATCHING DATE
WITH MaxGood AS (
SELECT E.RecordID, MAX(E.GoodDate) MaxDate
FROM @Employees E INNER JOIN @Records R ON E.RecordID = R.RecordID
WHERE E.GoodDate <= R.MatchDate
GROUP BY E.RecordID
), 
--CTE CONTAINS THE MAX BAD DATE THAT IS <= THE MATCHING DATE
MaxBad AS (
SELECT E.RecordID, MAX(E.BadDate) MaxDate
FROM @Employees E INNER JOIN @Records R ON E.RecordID = R.RecordID
WHERE E.BadDate <= R.MatchDate
GROUP BY E.RecordID
),
--CTE UNIONS THE MAX GOOD & BAD DATES INTO SINGLE TABLE
AllDates AS (
select RecordID, MaxDate from MaxGood UNION ALL select * from MaxBad
),
--CTE TAKES THE MAX DATE FROM BOTH AND SELECTS ONE ROW PER RECORD WITH THE CLOSES GOOD OR BAD DATE
DateMatch AS (
SELECT RecordID, MAX(MaxDate) MaxDate FROM AllDates
GROUP BY RecordID
)
--UPDATES THE @EMPLOYEE TABLE WHERE EITHER THE GOOD OR THE BAD MATCH (POTENTIAL TO UPDATE MULTIPLES IF OVERLAP BUT THAT'S OK)
UPDATE E
SET E.MatchedID = 1
FROM @Employees E INNER JOIN DateMatch M ON E.RecordID = M.RecordID
WHERE M.MaxDate = E.GoodDate OR M.MaxDate = E.BadDate

--ENDS UP WITH EMPID 2 SELECTED FOR RECORD 555, EMPID 9 SELECTED FOR RECORD 333
select * 
from @Employees E INNER JOIN @Records R ON E.RecordID = R.RecordID