[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

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

0
MikeMCSD
Asked:
MikeMCSD
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
strickddCommented:
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_csCommented:
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
 
CmdoProg2Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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
 
nap0leonCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now