Best query to pick row based on IP address and DateStamp

Question

Supposed I have Table1 that have account_id, ip_address and created_date columns but on the other table, Table2 has only ip_address and created_date columns. Table2 doesn't have an account_id that I can use to join in table1. For some reason from the management, I still need to assume or pick accounts_ids from Table1 to fill it in Table2.

What will be the best way to pick accounts_ids from Table1? I tried the query below but it seems that there are accounts from Table1 which is obviously doesn't belong to Table2 due to created_date comparison. I wonder if there are ways to do this such as comparing YEAR(create_date) or etc?

SELECT t2.ip_address, t2.created_date, t1.account_id 
FROM Table2 t2 
LEFT JOIN ( SELECT account_id, ip_address, MAX(created_date) created_date
            FROM Table1 t1
            GROUP BY account_id, ip_address  
           ) t1 
on t2.ip_address = t1.ip_address

EDIT: Note that I just want to pick the closest date as possible since IP address and dates are not unique on Table1.

Answer 1

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

Answer 2

select * 
from ( SELECT t2.ip_address, t2.created_date, t1.account_id 
            , row_number() over (partition by t2.ip_address 
                                 order by abs(datediff(dd, t1.created_date, t2.created_date)) as dd
       FROM Table2 t2 
       join Table1 t1
       on t2.ip_address = t1.ip_address
     ) tt
where dd = 1