Date Parameter in Derived Table used in more than one way

[iproz]

Has anyone been able to efficiently parameterize a derived table with a date parameter such that the date is used in two different ways?

For example, given the SQL:

select
  sum(revenue)
from orders
left outer join invoices on orders.id = invoices.order_id
where
  order_date = '2023-01-01'
  and invoice_date >= '2023-01-01'

I know how to parameterize it with a templated filter

{% condition date_filter %} sales.order_date {% endcondition %}
and {% condition date_filter %} sales.invoice_date {% endcondition %}

so that both date criteria would equal the date passed in, but is there a way to get the SQL to use the date passed in for the >= clause on the other date field above?

I am able to do it by instead parameterizing a date_dim table, joining to that, and using the date returned in the two criteria, but then BigQuery isn’t able to take advantage of partitioning.

Any help would be appreciated!