I have a table that shows [Price] changing over time, but they are specified with a [ValidToDate] and then I have another table that shows [SalesQty] and [Date]. The [CurrentPrice] is in another table. I am trying to calculate the total cost over time.
The cost should be:
[Price]*[SalesQty] when the [Date] is less than the [ValidToDate]
[Price]*[SalesQty] when the [Date] is between the previous [ValidToDate] and the next [ValidToDate]
[CurrentPrice]*[SalesQty] when the [Date] is greater than the last [ValidToDate] in the price table .
I am trying to figure out where to start. Logically I understand what needs to happen, but execution is stumping me.