to do this.
Here is some sample data combinations that would come from 2 different
tables.
Item Method Price Cost Percentage
A-1 L 100 50 10
A-2 D 110 55 15
A-3 U 90 40 65
Ok, here is what I want to do.
If L then 100 x (1+(10/100))
if D then 110 x (1-(15/100))
if U then 40 x (1+(65/100))
To produce 1 number per item.
I can do this obviously using multiple When Then Else statements.
Like
Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
end
However I was thinking there might just be a better way to do this that
I am not familar with, plus I could easily have 8-10 of these methods
that I need to code.
Any ideas on a better way?
Thanks in advance.On 25 Oct 2006 11:30:11 -0700, mike wrote:
Quote:
Originally Posted by
>I have a question on coding, just want to see if there is a better way
>to do this.
>
>Here is some sample data combinations that would come from 2 different
>tables.
>
>Item Method Price Cost Percentage
>A-1 L 100 50 10
>A-2 D 110 55 15
>A-3 U 90 40 65
>
>Ok, here is what I want to do.
>
>If L then 100 x (1+(10/100))
>if D then 110 x (1-(15/100))
>if U then 40 x (1+(65/100))
>
>To produce 1 number per item.
>
>I can do this obviously using multiple When Then Else statements.
>Like
>
>Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
>end
>
>
>However I was thinking there might just be a better way to do this that
>I am not familar with, plus I could easily have 8-10 of these methods
>that I need to code.
>Any ideas on a better way?
>Thanks in advance.
Hi Mike,
The obvious simplification is to use one case with multiple WHEN clauses
instead of nesting the CASE expressions, like this:
Case when method='L'
then Price * (1+(percentage/100))
when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
Another possible way to simplify this, depending on the nature of the
other methods, would be to use CASE expressions for the variable parts
of the formula. All formula's above start with either Price or Cost,
then multiply this with either the sum or the difference of 1 and
percentage/100. You could rewrite this as
CASE WHEN method IN ('L', 'D')
THEN Price
ELSE Cost
END * (1 + ((percentage / 100)
* CASE WHEN method = 'D' THEN -1 ELSE 1 END))
--
Hugo Kornelis, SQL Server MVP|||On 25 Oct 2006 11:30:11 -0700, "mike" <mike.a.rea@.gmail.comwrote:
Quote:
Originally Posted by
>I can do this obviously using multiple When Then Else statements.
>Like
>
>Case when method='L'
then Price * (1+(percentage/100))
else case when method='D'
then Price * (1-(percentage/100))
else Cost * (1+(percentage/100))
end
>end
>
>However I was thinking there might just be a better way to do this that
>I am not familar with, plus I could easily have 8-10 of these methods
>that I need to code.
You have overly complicated this CASE expression. It does NOT require
nesting, and either format will handle ten (or fourty) alternative
calculations without becoming awkward.
CASE WHEN method='L'
THEN Price * (1+(percentage/100))
WHEN method='D'
THEN Price * (1-(percentage/100))
WHEN method='U'
THEN Cost * (1+(percentage/100))
END
or:
CASE method
WHEN 'L' THEN Price * (1+(percentage/100))
WHEN 'D' THEN Price * (1-(percentage/100))
WHEN 'U' THEN Cost * (1+(percentage/100))
END
Roy Harvey
Beacon Falls, CT
