• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

SQL 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 need to add another condition :

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

Can I still use the Case statement to do this? thanks

0
MikeMCSD
Asked:
MikeMCSD
  • 2
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
You can nest Case Statements.  Not 100% sure if I followed your logic, but here is an example.
declare @myCosts table (Id int identity(1,1), myPrice money, myOldPrice money, myCalculatedPrice money)

insert into @myCosts values (6.00, 5.00, null)
insert into @myCosts values (10.00, 12.00, null)
insert into @myCosts values (45.00, 40.00, null)
insert into @myCosts values (2.00, 3.00, null)
insert into @myCosts values (24.00, 21.00, null)

update @myCosts

	set myCalculatedPrice = 	
	
	case 
		--if Old Price < 5
		when myOldPrice < 5 then 
			
			case 
				--the variance of "my old price" and "price" < 5 so subtract the variance from 5
				when (myOldPrice - myPrice) < 5 then (5 - (myOldPrice - myPrice)) 
				
				--the variance of "my old price" and "price" >- 5 so return myOldPrice
				else myOldPrice 
			end
		
		when myOldPrice < 10 then 
		
			case 
				--the variance of "my old price" and "price" < 5 so subtract the variance from 5
				when (myOldPrice - myPrice) < 8 then (8 - (myOldPrice - myPrice)) 
				
				--the variance of "my old price" and "price" >- 5 so return myOldPrice
				else myOldPrice 
			end
		
		
		when myOldPrice < 50 then 
		
		case 
				--the variance of "my old price" and "price" < 5 so subtract the variance from 5
				when (myOldPrice - myPrice) < 12 then (12 - (myOldPrice - myPrice)) 
				
				--the variance of "my old price" and "price" >- 5 so return myOldPrice
				else myOldPrice 
			end
		
	end																 

from @myCosts

select *
from	@myCosts

Open in new window

0
 
MikeMCSDAuthor Commented:
thanks gohord, ..  good idea about nesting.
I think this will work, I'll test it :

SET Price = OldPrice + 2.00 + (
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
      CASE
            WHEN (OldPrice - ProductCost) < 8.00  THEN 8.00 - (OldPrice - ProductCost)             
            ELSE OldPrice
      END

WHEN OldPrice BETWEEN 10 AND 50 THEN
      CASE
            WHEN (OldPrice - ProductCost) < 12.00  THEN 12.00 - (OldPrice - ProductCost)             
            ELSE OldPrice
      END

END
0
 
Christopher GordonSenior Developer AnalystCommented:
Just a heads up, a case statement will automatically exit when it reaches a condition that is TRUE.

Because of this, the BETWEEN syntax isn't necessary.   You'll notice in my sample that I didn't use BETWEEN.

I guess it just boils down to what is easier for you (or the next programmer) to understand.  I honestly couldn't tell you if there was a best practice on which way to go.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

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