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 !

Answer

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

WITH table_a(report_period, entity, tag, users_count, report_period_m1, report_period_d1) AS (
    SELECT * FROM VALUES
    ('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')
), table_b(report_period, entity, tag, users_count, report_period_d1) AS (
    SELECT * FROM VALUES
    ('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')
)
SELECT a.*
    ,b.*
FROM table_a AS a
LEFT JOIN table_b AS b
    ON a.report_period = b.report_period AND a.entity = b.entity
ORDER BY 1 desc, 2;

gives:

REPORT_PERIOD   ENTITY      TAG   USERS_COUNT   REPORT_PERIOD_M1    REPORT_PERIOD_D1    REPORT_PERIOD   ENTITY      TAG   USERS_COUNT   REPORT_PERIOD_D1
2021-03-31      entity 1    X     471           2017-05-31          2021-03-18          2021-03-31      entity 1    X     445           2021-03-18
2020-12-31      entity 2    A     135           2020-11-30          2021-03-18          null            null        null  null           null
2020-11-30      entity 3    X     402           2020-10-31          2021-03-18          null            null        null  null           null

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..

WITH table_a(report_period, entity, tag, users_count, report_period_m1, report_period_d1) AS (
    SELECT * FROM VALUES
    ('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')
), table_b(report_period, entity, tag, users_count, report_period_d1) AS (
    SELECT * FROM VALUES

    ('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')
)
SELECT a.*
    ,COALESCE(b.USERS_COUNT, a.USERS_COUNT) as b_USERS_COUNT
FROM table_a AS a
LEFT JOIN table_b AS b
    ON a.report_period = b.report_period AND a.entity = b.entity
ORDER BY 1,2;