• Status: Solved
• Priority: Medium
• Security: Public
• Views: 216

# CASE Statement with a variable

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
skull52
• 3
• 3
1 Solution

Commented:
You're on the right track

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

Author Commented:
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

Commented:
yup, absolutely
0

Author Commented:
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

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

Author Commented:
Derek,
Thanks for your help, all worked well.
0

## Featured Post

• 3
• 3
Tackle projects and never again get stuck behind a technical roadblock.