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