'Join tables with the nearest date
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|