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

x
?
Solved

CASE Statement with a variable

Posted on 2011-02-18
6
Medium Priority
?
215 Views
Last Modified: 2012-05-11
Hi all,
I have a script that outputs items with pricing and I have an issue that is beyond my level.  When PRICMTHD value = 4 it states that the value in UOMPRICE is a percent markup of the value in STDCOST in this case25% and will be the selling price, and it can vary, and if PRICMTHD value = 1 then the UOMPRICE value is the actual selling price. I need a way to calculate the selling price when it is a percent value and the actual value if it is not, if it were always 25% it would be easy, however it is not it could be 10%, 15%, Etc. I thought of using a CASE statement but I am not sure how to do a variable.  
CASE WHEN PRICMTHD = 4 THEN do something ELSE UOMPRICE END as SELLINGPRICE
 Row Image
0
Comment
Question by:skull52
[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
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 34929007
You're on the right track

select case when PRICMTHD = 4 then STNDCOST * (1 + (UOMPRICE / 100)) else UOMPRICE end
0
 

Author Comment

by:skull52
ID: 34929516
DEREK
That worked perfectly, I have one one part to that question, I appears that there are 2 more pricing methods that use percent 2 and 3 can I combined the case statement like    

CASE WHEN PRICMTHD = 4 then STNDCOST * (1 + (UOMPRICE / 100)) WHEN PRICMTHD = 2 then LISTPRCE * (1 + (UOMPRICE / 100))WHEN PRICMTHD = 3 then CURRCOST * (1 + (UOMPRICE / 100))  else UOMPRICE END as SELLINGPRICE
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34929524
yup, absolutely
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:skull52
ID: 34929629
Sorry, one more thing when the WHEN PRICMTHD = 2 it is % of List so if the item is $22.00 and  the UOMPRICE is 100 then it is 100% of LIST i.e. $22.00 how would I write that math statement in the CASE statement, WHEN PRICMTHD = 2 then LISTPRCE * (1 + (UOMPRICE / 100)) won't work
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34929682
Should be able to just remove the "1 +", so its List * UOM / 100
0
 

Author Closing Comment

by:skull52
ID: 34929760
Derek,
Thanks for your help, all worked well.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

636 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