Question on scripting

[mike]

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.

[Hugo Kornelis]

On 25 Oct 2006 11:30:11 -0700, mike wrote:

>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

[Roy Harvey]

On 25 Oct 2006 11:30:11 -0700, "mike" <mi********@gmail.comwrote:

>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