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
Medium Priority
215 Views
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

0
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
• 3
• 3

LVL 15

Accepted Solution

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

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

ID: 34929524
yup, absolutely
0

Author Comment

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

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

Author Closing Comment

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

## Featured Post

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…
###### Suggested Courses
Course of the Month11 days, 21 hours left to enroll