4 Replies Latest reply: Aug 15, 2017 7:06 AM by Sunny Talwar RSS

    Create continous price history per item, last known value

    Birkir Bjornsson

      Hi,

       

      I have a price history table with in our ERP system and I need to create an application which can give us the correct price and any given date from a master calendar.  So if a value is missing for a date then fetch the last known value.  This might be done in set expressions or Load. Maybe previous() could work?

       

      The table looks something like this

       

         

      ItemNoPriceDateCostPriceSalesPrice
      12003-02-10 00:00:00.000100110
      22003-02-10 00:00:00.00090120
      12004-02-10 00:00:00.00090105
      22005-02-10 00:00:00.00080100
      12005-02-10 00:00:00.000100105
      12005-02-15 00:00:00.000100120

      And the result should be something like this. The .(dots) represent data in between.

       

         

      ItemNoPriceDateCostPriceSalesPrice
      12003-02-10 00:00:00.000100110
      12003-02-11 00:00:00.000100110
      12003-02-12 00:00:00.000100110
      12003-02-13 00:00:00.000100110
      12003-02-14 00:00:00.000100110
      12003-02-15 00:00:00.000100110
      12003-02-16 00:00:00.000100110
      12003-02-17 00:00:00.000100110
      1...
      1...
      12004-02-10 00:00:00.00090105
      12004-02-11 00:00:00.00090105
      12004-02-12 00:00:00.00090105

       

       

      Any hints are welcome

       

      Kind Regards

      Birkir