CREATE TABLE Table1(account int, ip_address varchar(15), created_date date); INSERT INTO Table1 VALUES (1, '10.0.0.1', '20170101'), (1, '10.0.0.1', '20170201'), (1, '10.0.0.1', '20170301'), (2, '10.0.0.2', '20170201'), (3, '10.0.0.3', '20170201'); CREATE TABLE Table2(ip_address varchar(15), created_date date); INSERT INTO Table2 VALUES ('10.0.0.1', '20170201'), ('10.0.0.2', '20170201'), ('10.0.0.3', '20170201');

SELECT (SELECT TOP (1) account FROM Table1 t1 WHERE t1.ip_address = t2.ip_address AND t1.created_date >= t2.created_date ORDER BY t1.ip_address, t1.created_date desc) account, t2.ip_address, t2.created_date FROM Table2 t2