Case statement problem

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

LVL 16
MikeMCSDAsked:
Who is Participating?
 
strickddConnect With a Mentor Commented:
Is this what you mean?

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
)
0
 
tim_csConnect With a Mentor Commented:
Not 100% sure what you're trying to get.  Is this what you're looking for?

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
0
 
CmdoProg2Connect With a Mentor Commented:
The case has only one ELSE condition , but I believe that what you want is the following to return the OldPrice for the case when none of the conditions are meet:

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
)
0
The new generation of project management tools

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.

 
nap0leonCommented:
Perhaps you want this?

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
)
0
 
MikeMCSDAuthor Commented:
thanks all, . .   I meant this :

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

NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 5.00
      THEN 5.00 - (OldPrice - ProductCost)
       ELSE OldPrice                                    


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

NEED THIS INSTEAD :
If (OldPrice - ProductCost) < 8.00
      THEN 8.00 - (OldPrice - ProductCost)
       ELSE OldPrice    
                               
WHEN OldPrice BETWEEN 10 AND 50 THEN 12.00 - (OldPrice - ProductCost)

NEED THIS INSTEAD :
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?


0
 
tim_csCommented:
CASE
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
0
 
nap0leonCommented:
Sorry - my IF statement above has several errors in it... I was distracted while writing it up and using ELSE in multiple IFs I don't think it will do what you really want anyway.

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

0
 
MikeMCSDAuthor Commented:
I think you're right tim, . . brb need more coffee . .
0
 
MikeMCSDAuthor Commented:
I made a mistake . ., this is for SQL and not C#.  
Put in the wrong category. Sorry about that.
0
 
nap0leonConnect With a Mentor Commented:
What you are asking for, I think, is the first of the two I posted which is the same as tim_cs's post:

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
)
0
 
nap0leonCommented:
ARGH... need an edit button - the last section should read:

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
)
0
All Courses

From novice to tech pro — start learning today.