Solved

Case statement problem

Posted on 2011-09-28
11
181 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
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Service Controller 1 27
InputLanguage 1 33
Modal Popup Extender control 1 35
How to ensure the user enters a value in a date field in an ASP.NET application? 1 19
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

789 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