I'm not sure if this sample data cover your current issue.
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');
This solution assumes that, at least, there is a created_date
on your Table1
>= of the created_date
on Tabel2
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
account | ip_address | created_date
------: | :--------- | :------------------
1 | 10.0.0.1 | 01/02/2017 00:00:00
2 | 10.0.0.2 | 01/02/2017 00:00:00
3 | 10.0.0.3 | 01/02/2017 00:00:00
dbfiddle here
If there is no an exact date, you can build a list of periods:
WITH ipList as
(
SELECT account, ip_address, created_date,
COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
FROM Table1
)
select * from iplist;
GO
account | ip_address | created_date | next_date
------: | :--------- | :------------------ | :------------------
1 | 10.0.0.1 | 01/01/2017 00:00:00 | 01/02/2017 00:00:00
1 | 10.0.0.1 | 01/02/2017 00:00:00 | 01/03/2017 00:00:00
1 | 10.0.0.1 | 01/03/2017 00:00:00 | 01/03/2017 00:00:00
2 | 10.0.0.2 | 01/02/2017 00:00:00 | 01/02/2017 00:00:00
3 | 10.0.0.3 | 01/02/2017 00:00:00 | 01/02/2017 00:00:00
And then try to find the best match:
WITH ipList as
(
SELECT account, ip_address, created_date,
COALESCE(LEAD(created_date) OVER (PARTITION BY account, ip_address ORDER BY created_date), created_date) next_date
FROM Table1
)
SELECT (SELECT TOP(1) ipList.account
FROM ipList
WHERE t2.ip_address = ipList.ip_address
AND t2.created_date >= ipList.created_date
AND t2.created_date <= ipList.next_date) account,
t2.ip_address,
t2.created_date
FROM Table2 t2;
GO
account | ip_address | created_date
------: | :--------- | :------------------
1 | 10.0.0.1 | 01/02/2017 00:00:00
2 | 10.0.0.2 | 01/02/2017 00:00:00
3 | 10.0.0.3 | 01/02/2017 00:00:00
dbfiddle here