I have two tables to be joined. I’m trying to join the current month with last month’s record of each ID. I only want to join the previous date and value from Table B. What is the optimal way to join?
Table A
| ID | Date A | Utility |
|---|---|---|
| 111 | 202008 | $ 200 |
| 222 | 202007 | $ 300 |
Table B
| ID | Date B | Saving |
|---|---|---|
| 111 | 202008 | $200 |
| 111 | 202007 | $120 |
| 222 | 202007 | $900 |
| 222 | 202006 | $800 |
Expected table:
Table C
| ID | Date A | Utility | Date B | Saving |
|---|---|---|---|---|
| 111 | 202008 | $ 200 | 202007 | $120 |
| 222 | 202007 | $ 300 | 202006 | $800 |
>Solution :
Here’s a solution with Postgres.
select *
from t join t2 on t2.id = t.id and datediff(month, date_b, date_a) = 1
| ID | Date_A | Utility | ID | Date_B | Saving |
|---|---|---|---|---|---|
| 111 | 2020-08-01 | 200 | 111 | 2020-07-01 | 120 |
| 222 | 2020-07-01 | 300 | 222 | 2020-06-01 | 800 |