x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 313

# 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

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

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

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
0
MikeMCSD
2 Solutions

Commented:

UPDATE Nop_ProductVariant
SET Price =  OldPrice + (CASE
WHEN OldPrice < 5 THEN  5
WHEN OldPrice BETWEEN 5 AND 10 THEN 8
ELSE 12
END
- (OldPrice - ProductCost)) + 2.5
0

Sr. System AnalystCommented:
here if above does not work...
``````UPDATE Nop_ProductVariant
SET Price = OldPrice + 2.5 + (
CASE
WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)
WHEN OldPrice BETWEEN 10 AND 20 THEN 12.00 - (OldPrice - ProductCost)
END
)
``````
0

Commented:
<ot> nice one @jvejskrab. very elegant </ot>
0

Sr. System AnalystCommented:
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
)
``````
0

Sr. System AnalystCommented:
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
)
``````
0

Sr. System AnalystCommented:
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
)
``````
0

Author Commented:
nicely done, thanks guys
0

Commented:

Thanks @_agx_
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.