Lookups based on date ranges

OK, neglected by me, not by everyone else. At Pivotstream I have the benefit of a great SQL team. Generally speaking, when I need a calculated column, I ask them to provide it. The benefits of doing that are documented several places on this site, including here and continued here.

But hey, not everyone has a database rapid-response team at their disposal

So questions like this one come up a lot on the forums, reminding me of my blind spot: