0 Replies Latest reply on Nov 15, 2012 1:12 PM by Dane Hobbs

    Prices that change with time,  and Sales over time

    Dane Hobbs

      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.


      Any Suggesions?