Have you ever found yourself needing to do a join on a BETWEEN condition? If so, you have probably also been struggling with performance. The optimizer in SQL Server just cannot seem to produce a good execution plan for BETWEEN. Thanks to a lot of trial and error, we were however able to find a workaround!
This workaround, using a CROSS APPLY together with a SELECT TOP 1 … ORDER BY, should prove useful outside of the Anchor Modeling community as well. The example is a draw consisting of a bunch of floating point numbers, similar to the Fisher’s noncentral hypergeometric distribution, but this works similarly for date ranges.
Note that constructing the #sequence table only works when your intervals do not overlap. This is the extra information we give the optimizer when rewriting the query, hence making it run so much faster.