Solved

CASE Statement with a variable

Posted on 2011-02-18
6
208 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now