Have got two Bigquery tables as shown below:
Table 1:
Store Report_Date 11 2021-03-03 12 2021-03-03 11 2021-04-14 13 2021-04-14
Table 2:
Store 11 12 13
Scenario: Have to find missing Stores of Table 1 for each date comparing with Table 2 Stores.
Expected Output: To list each missing Stores for each date on comparing.
MissingStore Report_Date 13 2021-03-03 12 2021-04-14
Tried Query: But this query doesn’t shows up the respective Report_Date, instead it shows ‘null’.
WITH tab1 AS ( SELECT DISTINCT Store, Report_Date FROM tab1 ) SELECT DISTINCT tab2.Store, tab1.Report_Date FROM tab2 LEFT JOIN tab1 ON tab1.Store = tab2.Store WHERE tab1.Store IS NULL
Advertisement
Answer
Using a calendar table approach:
SELECT s.Store AS MissingStore, d.Report_Date FROM (SELECT DISTINCT Store FROM tab1) s CROSS JOIN (SELECT DISTINCT Report_Date FROM tab1) d INNER JOIN tab2 t2 ON s.Store = t2.Store LEFT JOIN tab1 t1 ON t1.Store = s.Store AND t1.Report_Date = d.Report_Date WHERE t1.Store IS NULL;