CODEWITHSUNDEEP
sqldatedataset
Jacob Ian
Jacob Ian

Reputation: 699

Join table by id and nearest date for every date

I have 2 tables:

TABLE 1

id      date_measured    value 1

1       01/01/2017       5
1       02/20/2017       6
1       04/01/2017       5
2       03/02/2017       5
2       04/02/2017       3

TABLE 2

id      date_measured    value 2

1       01/06/2017       5
1       03/01/2017       6
2       02/01/2017       5
2       03/09/2017       7
2       04/05/2017       4

I want to join it such that each id matches and the closest date matches so:

id      date_measured1     value 1      date_measured2      value 2

1       01/01/2017         5            01/06/2017          5
1       02/20/2017         6            03/01/2017          6
2       02/01/2017         5            02/01/2017          5
2       03/02/2017         5            03/09/2017          7
2       04/02/2017         3            04/05/2017          4

etc. IE for each id for each date measured take the closest measured date in the other table and make it a row. Something closeish to

 SELECT *
 FROM table1 a
 INNER JOIN table2 b
 ON a.id = b.id
       AND <date from a is closest date from b>

But I have no idea how to do the second part. Any suggestions?

Upvotes: 4

Views: 3130

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

If you have window functions use ROW_NUMBER():

SQL DEMO I use postgresql so date function may vary on your rdbms

WITH cte as (
    SELECT *, 
           t1.id as t1_id, 
           t1.date_measured as t1_date,
           t1.value1,
           t2.id as t2_id, 
           t2.date_measured as t2_date,
           t2.value2,
           date_part('day', age(t1.date_measured, t2.date_measured)) as days,
           ROW_NUMBER() OVER (PARTITION BY t1.id, t1.date_measured
                              ORDER BY abs(date_part('day', age(t1.date_measured, t2.date_measured)))
                              ) as rn

    FROM table1 t1
    JOIN table2 t2
      ON t1.id = t2.id
)
SELECT *
FROM cte
WHERE rn = 1 
ORDER BY t1_id, t1_date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

In standard SQL, you can get the date using a correlated subquery:

select t1.*,
       (select t2.date_measured
        from table2 t2
        where t2.id = t1.id
        order by abs(t2.date_measured - t1.date_measured) asc
        fetch first 1 row only
       ) as t2_date_measured
from table1 t1;

You can then join back to table2 to get additional information from that row.

The above is generic SQL (not necessarily standard SQL). Date/time functions tend to be peculiar to each database; so - may not work for the difference. Not all databases support fetch first 1 row only, but almost all support some mechanism for doing the same thing.

Upvotes: 5

Related Questions