How to use Date Range Field Filter Variable on Native SQL Subqueries

Hi, I’m looking for a way to use a common date range field filter variable on multiple subqueries.

Actual I have two simple date variables as you can see in this simplified example:

SELECT t1.total1, t2.total2

FROM (
    SELECT SUM(field1) AS total1 FROM table1
    WHERE date BETWEEN {{date_start}} AND {{date_end}}
) AS t1

LEFT JOIN ( 
    SELECT SUM(field2) AS total2 FROM table2
    WHERE date BETWEEN {{date_start}} AND {{date_end}}
) AS t2 ON t2.join = t1.join

On the dashboard I have the more advanced date range filter widget for other single table questions. I also want to connect it to this question. The goal is to use something like this:

SELECT t1.total1, t2.total2

FROM (
    SELECT SUM(field1) AS total1 FROM table1
    WHERE {{date_range}}
) AS t1

LEFT JOIN ( 
    SELECT SUM(field2) AS total2 FROM table2
    WHERE {{date_range}}
) AS t2 ON t2.join = t1.join

But it only works for one table because of the generated SQL:

t1.date BETWEEN %start% AND %end%

Is there any way to use the same field filter for both subqueries?

Hi @Data
You are hitting at least two issues - upvote by clicking :+1: on the first post of each issue:
https://github.com/metabase/metabase/issues/3324
https://github.com/metabase/metabase/issues/5781
There’s a hacky workaround, which might be okay for you depending on your data - otherwise I would recommend a lookup table with all dates:
https://github.com/metabase/metabase/issues/3324#issuecomment-416140719
https://github.com/metabase/metabase/issues/4478#issuecomment-387513533

Hi @flamber
thanks for pointing to these issues. I upvoted them and also could apply the workaround :+1: