How to use dange range with UNION query?

Get Help
1

Hello, I am trying to get a date range variable in a query that has UNION. Data is stored in BigQuery. Variable type of Date only supports a single value & not a range so I am using Field Filter.

SELECT
  *
FROM
(
  (
  SELECT
      TransactionID,
      CreatedDate
  FROM
    `bill_payment.transactions_1`
  )
  UNION ALL
  (
    SELECT
        TransactionID,
        CreatedDate
    FROM
      `bill_payment.transactions_2`
  )
)
WHERE
    {{CreatedDate}};

The date range filter should apply to both tables. The problem is, in field filter I can select only 1 table. So whether I put the field filter variable outside or within the subqueries, it results in an error.

Is there a way I can make this work using a single date range field? Or is this not possible & I have to make 2 separate fields i.e. fromdate and todate?

2

Try creating a model, then write a question from the model.

Alternatively, create a view on the database that contains both of those tables.

I've moved away from creating questions using SQL as you very quickly hit some odd limitations. Metabase does a great job of being clever with tables and views, so work with it!