Solved

Case statement problem

Posted on 2011-09-28
11
183 Views
Last Modified: 2012-05-12
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
Comment
Question by:MikeMCSD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 28

Accepted Solution

by:
strickdd earned 125 total points
ID: 36717858
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
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 125 total points
ID: 36717863
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
 
LVL 12

Assisted Solution

by:CmdoProg2
CmdoProg2 earned 125 total points
ID: 36717867
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:nap0leon
ID: 36717868
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36718018
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
 
LVL 15

Expert Comment

by:tim_cs
ID: 36718111
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
 
LVL 18

Expert Comment

by:nap0leon
ID: 36718123
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36718148
I think you're right tim, . . brb need more coffee . .
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36718211
I made a mistake . ., this is for SQL and not C#.  
Put in the wrong category. Sorry about that.
0
 
LVL 18

Assisted Solution

by:nap0leon
nap0leon earned 125 total points
ID: 36718217
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
 
LVL 18

Expert Comment

by:nap0leon
ID: 36718226
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question