installment_table:
| acc_no | installment_no | due_date |
|---|---|---|
| 12 | 1 | 2022-04-22 |
| 12 | 2 | 2022-05-22 |
| 12 | 3 | 2022-06-22 |
| 12 | 4 | 2022-07-22 |
| 12 | 5 | 2022-08-22 |
transaction_table:
| acc_no | txn_date |
|---|---|
| 12 | 2022-04-22 |
| 12 | 2022-05-24 |
| 12 | 2022-06-18 |
| 12 | 2022-07-18 |
| 12 | 2022-08-25 |
resulting_table:
| acc_no | installment_no | due_date | txn_date | days_diff |
|---|---|---|---|---|
| 12 | 1 | 2022-04-22 | 2022-04-22 | 0 |
| 12 | 2 | 2022-05-22 | 2022-05-24 | 2 |
| 12 | 3 | 2022-06-22 | 2022-06-18 | -4 |
| 12 | 4 | 2022-07-22 | 2022-07-18 | -4 |
| 12 | 5 | 2022-08-22 | 2022-08-25 | 3 |
I want to join the tables based on the account number and the nearest transaction date. Is there a way to do it in MySQL 8.0.37?
CodePudding user response：
You can use a lateral join to evaluate the days difference and select the minimum for each row:
select *
from installment i,
lateral(
select txn_date, DateDiff(txn_date, due_date) days_diff
from transaction t
where t.acc_no = i.acc_no
order by Abs(DateDiff(txn_date, due_date))
limit 1
)t;
See Example fiddle