I have two tables
with [EffectiveStart] and [EffectiveEnd] dates in each row. These dates signify which payment rates to use.
has daily totals.
In my query, I compute a dollar amount by multiplying the payment rates by the daily totals. The problem is the rates change annually and need to specify in the query which rates to use based on the [EffectiveStart] EffectiveEnd] dates. In my query I have a [claimdate] field ( which is the end of every month). I need the query to look at the [claimdate] and determine which rate to use if it is between the [EffectiveStart] [EffectiveEnd] dates.