4 Replies Latest reply: Jul 3, 2015 4:18 AM by Manish Kachhia RSS

    How to Associate to the Closest Date

      Hi all,

       

      I have two date fields that I need to associate together to the nearest occurrence. We have OrderDate, which is when an order is actually placed, and HistoricalDate, associated to a change in Average Cost for the specific product.

       

      For example:

       

                    ORDER TABLE                                            COST TABLE

      OrderID     SkuID          OrderDate                SkuID          HistoryDate            Cost     

          1              123              1/1/2015                 123              12/31/2014              $3.00

          2              123              1/2/2015                 123              1/3/2015                  $3.50

          3              123              1/3/2015                  

       

       

      In this example, both OrderID's 1 and 2, should be associated with the cost of HistoryDate 12/31/2014, while Order 3 should be associated with the cost of HistoryDate 1/3/2015.

       

      Any suggestions?