I have two tables (t1
, t2
) to join based on id and year-month YYYY-MM
Generally, the join goes like this for example:
SELECT t1.id, t1.month, t2.price
FROM t1
JOIN t2
ON t1.id = t2.id AND t1.year_month = t2.year_month
Now, for one specific t1.id
, say t1.id = 'a'
, I need to join with the previous month on t2
I.E.
SELECT t1.id, t1.month, t2.price
FROM t1
JOIN t2
ON t1.id = t2.id
AND (t1.year_month = t2.year_month if t1.id != 'a'
else t1.year_month = t2.year_month - 1 month)
What I think to do is to create a temp table that year-month on t2
for id = 'a'
is increased by one month. But t2
is a really large table so I don’t really want to go with this route.
What is the best way to achieve this? Thanks!