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.
