asked on

Update a Price field with some calculations.

I need to Update the Price field with this critera :

IF OldPrice < 5.00
Price =
1. add the difference of : (5.00 - (OldPrice - ProductCost)) to OldPrice
2. add 2.50

IF OldPrice >= 5.00 AND <= 10.00
Price =
1. add the difference of : (8.00 - (OldPrice - ProductCost)) to OldPrice
2. add 2.50

IF OldPrice >= 10.00 AND <= 20.00
Price =
1. add the difference of : (12.00 - (OldPrice - ProductCost)) to OldPrice
2. add 2.50

Should produce this example data :

The field : OldPrice ProductCost
data : 4.95 3.76

1. (5.00 - (4.95 - 3.76)) = 3.81 Price = 4.95 + 3.81 = 8.76
2. Price = 8.76 + 22.50 = 11.26

UPDATE Nop_ProductVariant
SET Price = OldPrice ProductCost ??

What is the best way to code this? thanks

ASKER CERTIFIED SOLUTION

jvejskrab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

<ot> nice one @jvejskrab. very elegant </ot>

HainKurt

which is equal to

UPDATE Nop_ProductVariant
SET Price = 2.5 + (
CASE 
WHEN OldPrice < 5 THEN 5.00 + ProductCost
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 + ProductCost
WHEN OldPrice BETWEEN 10 AND 20 THEN 12.00 + ProductCost
END
)

Open in new window

HainKurt

which is

UPDATE Nop_ProductVariant
SET Price = (
CASE 
WHEN OldPrice < 5 THEN 7.50 + ProductCost
WHEN OldPrice BETWEEN 5 AND 10 THEN 10.50 + ProductCost
WHEN OldPrice BETWEEN 10 AND 20 THEN 14.50 + ProductCost
END
)

Open in new window

HainKurt

which is

UPDATE Nop_ProductVariant
SET Price = ProductCost + (
CASE 
WHEN OldPrice < 5 THEN 7.50
WHEN OldPrice BETWEEN 5 AND 10 THEN 10.50
WHEN OldPrice BETWEEN 10 AND 20 THEN 14.50
END
)

Open in new window

MikeMCSD

ASKER

nicely done, thanks guys

jvejskrab

Thanks @_agx_