Solved

CASE Statement with a variable

Posted on 2011-02-18
6
212 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 500 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

729 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