|
|
Posted by Hugo Kornelis on 10/25/06 20:02
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
Navigation:
[Reply to this message]
|