Multiplying a Column by -1 Based on Another Column

Question

I'm writing an SSRS report that will change the value of one of the fields based on the value of another field in the same row. Would the best way to do it via SQL?

i.e.-Multiply FieldX (float) by -1 if the value of FieldY ends with D,DA, or 'DB', etc.

I was initially looking at using a case statement, however I was getting a little stuck at work today. Any ideas? The SQL already uses a union, sums and grouping.

Answer 1

Here's how to do it in SQL with a case statement. You can always break up the ors into their own when...then block, but it works just as well this way. Enjoy!

select
    case
        when right(FieldY, 1) = 'D'
             or right(FieldY, 2) = 'DA'
             or right(FieldY, 2) = 'DB'
        then FieldX * (-1)
        else FieldX
    end as FieldX
from
    table

Now, if you want to know if it ends with 'D' or 'D?' where '?' is any letter, then:

select
    case
        when right(FieldY, 1) = 'D' then FieldX * (-1)
        when left(right(FieldY, 2), 1) = 'D' then FieldX * (-1)
        else FieldX
    end as FieldX
from
    table