Identify product sales made >90 days after delivery

[ingoniclas]

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.

Many thanks for any help.

[qliksus]

Hi,

I don't know about ur data model, sorry in advance if i repeat something which you have already tried.

You need to design your data model such a way it looks like (IF I UNDERSTOOD UR BUSINESS LOGIC)

Prod Id     DelDtae          SalDate

A             01/01/2012     02/15/2012

A             01/05/2012     04/10/2012 

Above is required if you have primary key as Product Id, if you have diff primary  key then use that.

if you have primary key is Product Id, then you can achieve by using MAX, MIN functions

[ingoniclas]

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.