With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

I have this :

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

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 50 THEN 12.00 - (OldPrice - ProductCost)

END

)

but I need to add another condition :

Instead of :

WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)

I need this :

If (OldPrice - ProductCost) < 5.00

THEN 5.00 - (OldPrice - ProductCost)

ELSE OldPrice << need to add that

I need to apply this to the other "WHEN" conditions too.

How do I code this? thanks

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

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 50 THEN 12.00 - (OldPrice - ProductCost)

END

)

but I need to add another condition :

Instead of :

WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)

I need this :

If (OldPrice - ProductCost) < 5.00

THEN 5.00 - (OldPrice - ProductCost)

ELSE OldPrice << need to add that

I need to apply this to the other "WHEN" conditions too.

How do I code this? thanks

CASE

WHEN OldPrice < 5 THEN CASE WHEN (OldPrice = ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost) ELSE OldPrice END

WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)

WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)

END

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

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 50 THEN 12.00 - (OldPrice - ProductCost)

ELSE OldPrice

END

)

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

CASE

WHEN (OldPrice - ProductCost) < 5 THEN 5.00 - (OldPrice - ProductCost)

WHEN (OldPrice - ProductCost) BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)

WHEN (OldPrice - ProductCost) BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)

ELSE OldPrice

END

)

Otherwise it seems a straightforward conversion of what you already have...

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

If ((OldPrice - ProductCost) < 5)

THEN

5 - (OldPrice - ProductCost)

ELSE

OldPrice

If (BETWEEN 5 and 10)

THEN

8 - (OldPrice - ProductCost)

ELSE

OldPrice

If (BETWEEN 10 and 20)

THEN

12 - (OldPrice - ProductCost)

ELSE

OldPrice

END

)

CASE

WHEN OldPrice < 5 THEN 5.00 - (OldPrice - ProductCost)

If (OldPrice - ProductCost) < 5.00

THEN 5.00 - (OldPrice - ProductCost)

ELSE OldPrice

WHEN OldPrice BETWEEN 5 AND 10 THEN 8.00 - (OldPrice - ProductCost)

If (OldPrice - ProductCost) < 8.00

THEN 8.00 - (OldPrice - ProductCost)

ELSE OldPrice

WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)

If (OldPrice - ProductCost) < 12.00

THEN 12.00 - (OldPrice - ProductCost)

ELSE OldPrice

END

Needed to change all the "WHEN"'s.

nap0leon, does your If statement do this?

WHEN (OldPrice - ProductCost) < 5.00 THEN 5.00 - (OldPrice - ProductCost)

WHEN (OldPrice - ProductCost) < 8.00 THEN 8.00 - (OldPrice - ProductCost)

WHEN (OldPrice - ProductCost) < 12.00 THEN 12.00 - (OldPrice - ProductCost)

ELSE OldPrice

END

I think what you want is a variation of the first one (using WHENS and IFs) Gimme a minute to re-write it.

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

CASE

WHEN ((OldPrice - ProductCost) < 5) THEN 5 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 8) THEN 8 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 12) THEN 12 - (OldPrice - ProductCost)

ELSE OldPrice

END

)

But running it through some test data, I don't think it is doing what you really want for cases where marginal profit is > 12.

Consider the following:

Case for marginal profit < 5 (e.g., 2)

OldPrice = 20

ProductCost = 18

Price = 20 + 2 + (5-2) = 25

Case for marginal profit > 5 but less than 8 (e.g., 6)

OldPrice = 20

ProductCost = 14

Price = 20 + 2 + (8-6) = 24

Case for marginal profit > 8 but less than 12 (e.g., 10)

OldPrice = 20

ProductCost = 10

Price = 20 + 2 + (12-10) = 24

Case for marginal profit > 12 (e.g., 15)

OldPrice = 20

ProductCost = 5

Price = 20 + 2 + (15) = 37

Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

CASE

WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)

ELSE OldPrice

END

)

Perhaps instead you want the Price to be simply the OldPrice with no modifications, in which case you would want this:

UPDATE Nop_ProductVariant

SET Price = (

CASE

WHEN ((OldPrice - ProductCost) < 5) THEN OldPrice + 2.00 + 5 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 8) THEN OldPrice + 2.00 + 8 - (OldPrice - ProductCost)

WHEN ((OldPrice - ProductCost) < 12) THEN OldPrice + 2.00 + 12 - (OldPrice - ProductCost)

ELSE OldPrice

END

)

All Courses

From novice to tech pro — start learning today.

UPDATE Nop_ProductVariant

SET Price = OldPrice + 2.00 + (

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 50 THEN 12.00 - (OldPrice - ProductCost)

ELSE OldPrice

END

)