Derive the total price of the quantities based on the Price range in the product table

[Anonymous]

Task 3:

SALES:

PRODUCT,DATE,QUANTITY

A,01-JAN-14,5

A,02-JAN-14,10

A,03-JAN-14,5

A,04-JAN-14,12

A,05-JAN-14,3

A,06-JAN-14,5

B,01-JAN-14,5

B,02-JAN-14,10

B,03-JAN-14,5

B,04-JAN-14,12

B,05-JAN-14,3

B,06-JAN-14,5

];

PRODUCT:

PRODUCT START  END  PRICE

A                   1           3        100

A                   4           15      110

A                 16          30       120

A                  31         40       150

The task is to derive the output in the below format:

The pricing of the quantities will be set according to the cumulative sum of the quantities:

Example:

If Qty = 5, Price = 100*3 + 110*2= 520 (5 Quantities will be under the slab 1-3 (3 Quantities) and also 4-15 (2 Quantities)

if Qty = 10, then pricing starts from the 6th quantity avoiding the calculation of price based on first 5 quantities. i.e. If Qty = 10, Then Pricing starts from the 6th quantity where the price is 110*10 = 1100, as per the 4-15 slab in product table.