PostgresQL - Getting nearest previously available data when date is missing in a join

Question

Given these two tables:

t1:

curr_date  | rate
2021-01-01 | 1.21
2021-01-04 | 1.22
2021-01-05 | 1.23

t2:

trx_date   | amount
2021-01-01 | 5430
2021-01-02 | 9842
2021-01-03 | 4684
2021-01-04 | 6541
2021-01-05 | 8972

I join them both with a query like this:

SELECT
    t2.trx_date,
    t1.curr_date,
    t1.rate,
    t2.amount,
    t1.rate * t2.amount AS net_amt
FROM t1
LEFT JOIN t2 ON t1.curr_date = t2.trx_date

Result:

trx_date   | curr_date  | rate | amount | net_amt
2021-01-01 | 2021-01-01 | 1.21 | 5430   | xxxxxx
2021-01-02 | NULL       | NULL | 9842   | NULL
2021-01-03 | NULL       | NULL | 4684   | NULL
2021-01-04 | 2021-01-04 | 1.22 | 6541   | xxxxxx
2021-01-05 | 2021-01-05 | 1.23 | 8972   | xxxxxx

Desired:

trx_date   | curr_date  | rate | amount | net_amt
2021-01-01 | 2021-01-01 | 1.21 | 5430   | xxxxxx
2021-01-02 | 2021-01-02 | 1.21 | 9842   | xxxxxx
2021-01-03 | 2021-01-03 | 1.21 | 4684   | xxxxxx
2021-01-04 | 2021-01-04 | 1.22 | 6541   | xxxxxx
2021-01-05 | 2021-01-05 | 1.23 | 8972   | xxxxxx

How can we modify the query such that the rate data is available, using the nearest previously available data?

. . You probably want the left join with t2 as the first table, not t1. — Gordon Linoff
– Gordon Linoff, Commented Sep 1, 2021 at 14:51

Gordon Linoff · Accepted Answer · 2021-09-01 14:48:06Z

1

You can use a lateral join:

SELECT t2.trx_date, t1.curr_date, t1.rate, t2.amount,
       t1.rate * t2.amount AS net_amt
FROM t2 LEFT JOIN LATERAL
     (SELECT t1.*
      FROM t1
      WHERE t1.curr_date <= t2.trx_date
      ORDER BY t1.curr_date DESC
      LIMIT 1
     ) t2
     ON 1=1;

answered Sep 1, 2021 at 14:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgresQL - Getting nearest previously available data when date is missing in a join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related