CODEWITHSUNDEEP
sqlsql-serversql-server-2005reporting-services
Audioillity
Audioillity

Reputation: 313

Multiplying a Column by -1 Based on Another Column

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.

Upvotes: 1

Views: 725

Answers (1)

Eric
Eric

Reputation: 95123

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

Upvotes: 2

Related Questions