It does not matter for the optimiser in a join.

Range definitions don't affect the choice of plans.

I wrote pretty complicated reports where different products had different aggregation periods.

My joins looked like this:

ON EventDate => PeriodStart and EventDate <

Case PeriodType when 'Week' then DATEADD(wk, NoOfPeriods, PeriodStart)

When 'Month' then DATEADD(mm, NoOfPeriods, PeriodStart)

... Etc.

Not a hick up on a compilation of a report aggregating 100 mil records.

And no change of code required if a product definition(s) changed.

For some products BOM counted from the date when, say, a mortgage was taken.

So, even PeriodStart is individual for every customer.

Therefore it cannot a value from a table, it must be a calculation based on product initiation date and a calendar applied to that product.

And you know - it all works. Done it, surprisingly successfully.