BigQuery SQL: How to find missing Values on comparing two tables over date range?

user_7818560 0
Viewed 646

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
missing-data google-bigquery left-join sql
2 Answers

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

Consider below approach

select a.Store MissingStore, b.Report_Date
from table2 a
cross join (select distinct Report_Date from table1) b
left join table1 c
using(Store, Report_Date)
where c.Report_Date is null

If applied to sample data in your question - output is

enter image description here

user_52219440
Related