Hi Experts,
Here is my table records,
SHIPMENT_ID SHIP_PRICE SHIP_ADDT_PRICE PRODUCT_ID PRODUCT_QTY
1000 3.95 1 12 2
1000 0 0 17 1
1000 6.95 2 11 4
1001 0 0 17 1
1001 12.95 1 12 2
1001 12.95 2 11 4
1002 0 0 17 1
1002 20.95 1 12 2
1002 20.95 2 11 4
For Each Shipment:
I need to get maximum shipprice (no need to consider product and qty).
then to manipulate as follows from existing sample records such as
SHIPMENT_ID SHIP_PRICE
1000 (1*6.95)+(4-1*2) -- Maximum Price Calculation for particular shipment
1000 (1*0) -- Other product calculation for same shipment
1000 (4*2) -- Other product calculation for same shipment
1001 (1*12.95)+(4-1*2) -- Maximum Price Calculation for particular shipment
1001 (1*0) -- Other product calculation for same shipment
1001 (4*2) -- Other product calculation for same shipment
1002 (1*20.95)+(4-1*2) -- Maximum Price Calculation for particular shipment
1002 (1*0) -- Other product calculation for same shipment
1002 (4*2) -- Other product calculation for same shipment
Max Shipprice Row for each Shipment
(SHIP_PRICE)+(remaining PRODUCT_QTY if PRODUCT_QTY>1 + SHIP_ADDT_PRICE)
Other than Max shipprice for each shipment
(PRODUCT_QTY + SHIP_ADDT_PRICE)
I am struggling here with logic how to proceed it, Any suggestions related to this?
Thanks,