asked on

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

ASKER CERTIFIED SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

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

derekkromm

yup, absolutely

Fred Webb

ASKER

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

derekkromm

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

Fred Webb

ASKER

Derek,
Thanks for your help, all worked well.