Progressive Pricing with additional Variable

Hi All,

I have a progressive pricing problem with a twist that I cant seem to figure out without making the formula ridiculously complex (likely needlessly so).

My pricing model is this:
Product Units Price
A 0 - 2500 $10
A 2500+ $5
B 0 - 10,000 $5
B 10,000+ $3

I have a table which I enter orders into which needs to automatically update the price based on the cumulative units sold.

So for example:
Order 1 is for 2000 units of A therefore the price should show as $10 each and the total should be $20,000
Order 2 is for another 2000 units of A. The price of the first 500 units should be $10 (the remainder of the 2500 available A units at $10) with the 1500 remaining units being priced at $5. Total would be $12,500 (500x$10 + 1500x$5).
Order 3 is for 1000 units of A, but with the 2500 units at $10 gone, this order and all orders from here on for A would be at $5.

Same situation as above for B.

My rate field in the orders table needs to take into account if the item is A or B (or C etc, from lookup table), how many units the order is for and how many units are remaining of the initial pricing category to determine the rate. Keep in mind that one order may be at two different rates if the order bridges the two pricing categories.

Example of order table below (so I need a function to fill in the rates and one to calculate total ... or a better idea on how to handle this)


Order Product Cust. Date Units Rate 1 Rate 2 Total
1 A 12 8/26/2013 2000 $10.00 $20,000.00
2 A 12 8/26/2013 2000 $10.00 $5.00 $12,500.00
3 A 13 8/26/2014 1000 $5.00 $5,000.00
4 B 12 12/1/2013 12000 $5.00 $3.00 $56,000.00
5 B 13 12/25/2013 5000 $3.00 $15,000.00
6
7


Thanks!