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]

       

      and

       

      [Price]*[SalesQty] when the [Date] is between the previous [ValidToDate] and the next [ValidToDate]

       

      and

       

      [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?