Skip to content
Advertisement

How to do a join to get previous days values?

I want to join tables as follow:

Report_Period Entity Tag Users Count Report_Period_M-1 Report_Period_D-1
2021-03-31 entity 1 X 471 2017-05-31 2021-03-18
2020-12-31 entity 2 A 135 2020-11-30 2021-03-18
2020-11-30 entity 3 X 402 2020-10-31 2021-03-18

With a view containing Day – 1 results as follow :

Report_Period Entity Tag Users Count Report_Period_D-1
2021-03-31 entity 1 X 445 2021-03-18
2021-03-31 entity 2 A 135 2021-03-18
2021-03-31 entity 3 X 402 2021-03-18

My Aim is at least to return results like this :

Report_Period Entity Tag Users Count Users Count D-1 Report_Period_M-1
2021-03-31 entity 1 X 471 445 2021-02-28
2020-12-31 entity 2 A 135 NULL 2020-11-30
2020-11-30 entity 3 X 402 NULL 2020-10-31

If i use classic joins on Report_Period,Entity & Tag, i will only return results rows which contains report period 2021-03-31.

Is it possible to replace the users count values for the current period ? If i want to replace Users Count values with Users Count D-1 for the current month ?

Your help is welcome and thank you for your time !

Advertisement

Answer

Based on your example it just looks like you want a LEFT JOIN

gives:

But for the statement of “replacing values” I can but assume you mean you then want to COALESCE b.users_count with a.users_count when b did not match thus the code becomes..

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement