Creating A Daterange For Multiple Columns

Get Help
#1

I have a query that looks something like the following:

SELECT

	//Some rows to select

FROM Table

LEFT JOIN (
	SELECT
	
		//Some rows from TableA
	
	FROM TableA
	
	WHERE TableA.StartDate BETWEEN {{StartDate}} AND {{EndDate}}
) AS SubqueryA

ON Table.A_id = TableA._id

LEFT JOIN (
	SELECT
	
		//Some rows from TableB
	
	FROM TableB
	
	WHERE TableB.UpdatedAt BETWEEN {{StartDate}} AND {{EndDate}}
) AS SubqueryB

ON Table.B_id = TableB._id

While this functions, I'd like to have a DateRange like used with field filters with a rolling default date range on multiple columns. I was looking around and couldn't find anything on the docs.

Could anyone point me to any good solutions or feature request pages? I couldn't manage to find much related to this

#2

if you want to use defaults I would use something like optional clauses https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses

#3

@Luiggi I'm familiar with optional clauses. It's more that the only non-FieldFilter date object is a single 'static' date. Whearas I'd like to have something akin to:

WHERE TableA.StartDate {{DateRange}}

and

WHERE TableB.UpdatedAt {{DateRange}}

Where DateRange is a singular date range that looks at the previous year. Or a field filter where I can select multiple DateTime columns from different tables.

#4

@nick-sbp Field Filters generate SQL that you cannot control, where it references the table.
https://www.metabase.com/learn/sql-questions/field-filters
You would have to have another lookup table, which contains all dates, which you then use later on example via a CTE. It will become a lot more complex than using single Date filters.