Identify product sales made >90 days after delivery
I have the following challenge: I deliver product A to retail stores. Each retail store gets a 10% rebate back from me for every product sold. However, they retail stores sometimes confuse my product A with product B from another company and incorrectly claim rebates from me. In order to see which claims are incorrect I want to make use of the fact that my product A is only good for 90 days after delivery. In order to check on that I want to link my table with the records of my deliveries with each retailers' sales report via the product ID and automatically identify sales made >90 days after my last delivery. How can I achieve this? See an example below:
table with my deliveries to the wholesaler:
Product ID Delivery date Price
A 01/01/2012 20 $
A 01/05/2012 20 $
report of the wholesaler to claim his rebates:
Product ID Sales date Claimed rebate
A 02/15/2012 2 $ -> correct
A 04/10/2012 2 $ -> incorrect, no rebate to be disbursed since it was sold > 90 days after my last delivery. It can't be my product A.
Re: Identify product sales made >90 days after delivery
Thanks for your idea. However, this is not exactly what I need. I need a system that checks if an apparent sale of my product A as reported by a retailer actually could have taken place. I want realize this by looking up if up to 90 days earlier I actually supplied this product A. And this can not be solved by simply using MIN or MAX since sometimes I want to analyze data spanning many, many months.