Return A Value based on condition of field not being NULL

[Corr]

I have a Table with a list of Defendants in legal suits. I have a report that goes out and one of the fields in the report needs to be a 1 = Loan Signer being sued, 2 = Co-signer of loan being sued, or 3 = Both loan signer and co-signer are being sued.

Each record contains a unique field call Debt#. The original loan signer and co-signer fields can sometimes be blank. These fields are FD1 (Name) and FD1A1 (Address) for loan signer and FD2 and FD2A1 for co-signer.

I need a query that returns a value of:

"1" if FD1 or FD1A1 IS NOT NULL
"2" if FD2 or FD2A1 IS NOT NULL or
"3" if FD1, FD1A1, FD2, and FD2A1 ARE NOT NULL

I was thinking somethink like IF FD1 or FD1A1 are not null then return a "1" if they are both blank then a "0" IF FD2 or FD2A1 are not null then return a "2" if they are both blank then a "0". Then add the two values togeather to get my result. Make sense? Please help as I am starting to pull out large amounts of hair.


Thanks,

John

 

[vongrunt]

What if FD1 is not null, FD2 is not null, but FD1A1 or FD2A1 are null?

Or perhaps condition for "3" is not accurate?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Corr]

Think of FD1 and FD1A1 as one field. SAme for FD2 and FD2A1. The reason I chose two fields each is because sometimes the database does not have the name field populated but the address field is and visa versa.

So if FD1 IS NULL and FD1A1 IS NOT NULL then the value would still be "1"

(FD1, FD1A1) IF one of these are not null return a "1" if both are null return a "0"

(FD2, FD2A1) IF one of these are not null return a "2" if both are null return a "0"

At the end add the two values. I should then get a 1,2, or 3. I am open to suggestions if anyone has an easier or more efficient way of doing this. Most of you guys can probably program circles around me, so feel free to say something if I am approaching this the wrong way.


Thanks,

John

 

[vongrunt]

Your approach looks good:

select
0 +
case when FD1 is not null or FD1A1 is not null then 1 else 0 end +
case when FD2 is not null or FD2A1 is not null then 2 else 0 end as myValue
from myTable

myValue is of type int; if you need a string, use CONVERT(char(1), ...) on entire expression.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)