Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Case statement problem

Posted on 2011-09-28
3
Medium Priority
?
257 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 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
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
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36718590
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 36718745
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
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 2000 total points
ID: 36718808
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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

609 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