SQL Case with conditions...

Question

Hi,

I have a query set up that contains a CASE statement which works 95% of the time... That other 5% is due to the data not being there. There is other data that indicates pointers that will help... I just know if this is possible...

So: CASE PRDE.STATUSCODE WHEN 'A' THEN 'Appealed' WHEN 'D' THEN 'Dismissed' WHEN 'P' THEN 'Pending' WHEN 'S' THEN 'Satisfied' WHEN 'T' THEN 'Settled' END AS STATUS

Sometimes the field is '' but a text field called SATISFIEDDATE maybe populated... Can I write something here like: CASE '' THEN [if SATISFIEDDATE <> '' then 'Satisfied']

Sorry if this is stupidly easy... :)

Answer 1

You can have another CASE statement:

CASE '' THEN CASE WHEN SatisfiedDate != '' THEN 'Satisfied' END

An alternative would be like this:

CASE
    WHEN PRDE.STATUSCODE = 'A' THEN 'Appealed'
    WHEN PRDE.STATUSCODE = 'D' THEN 'Dismissed'
    WHEN PRDE.STATUSCODE = 'P' THEN 'Pending'
    WHEN PRDE.STATUSCODE = 'S'
       OR (PRDE.STATUSCODE = '' AND SatisfiedDate != '') THEN 'Satisfied'
    WHEN PRDE.STATUSCODE = 'T' THEN 'Settled'
END AS STATUS

Answer 2

You can use the ELSE

ELSE else_result_expression

In else_result_expression you can put what ever legal expression you want, including what you wrote.

http://www.databasejournal.com/features/mssql/article.php/3288921/T-SQL-Programming-Part-5---Using-the-CASE-Function.htm

Answer 3

CASE
    WHEN PRDE.STATUSCODE='A' THEN 'Appealed'
    WHEN PRDE.STATUSCODE='D' THEN 'Dismissed'
    WHEN PRDE.STATUSCODE='P' THEN 'Pending'
    WHEN PRDE.STATUSCODE='S' OR (PRDE.STATUSCODE='' AND LEN(SatisfiedDate)>0) THEN 'Satisfied'
    WHEN PRDE.STATUSCODE='T' THEN 'Settled'
    ELSE '?null/unknown?'
END AS STATUS

Answer 4

you can do a nexted case statements, either in your else clause or in a when clause

CASE PRDE.STATUSCODE 
    WHEN 'A' THEN 'Appealed' 
    WHEN 'D' THEN 'Dismissed' 
    WHEN 'P' THEN 'Pending' 
    WHEN 'S' THEN 'Satisfied' 
    WHEN 'T' THEN 'Settled' 
    else
     case 
      when SATISFIEDDATE is not null then 'Satisfied'
     end
END AS STATUS

Answer 5

What happens when SATISFIEDDATE is not null, but the STATUSCODE is not S? What does that mean?

Answer 6

Maybe this is what you're looking for:

CASE PRDE.STATUSCODE 
WHEN 'A' THEN 'Appealed' 
WHEN 'D' THEN 'Dismissed' 
WHEN 'P' THEN 'Pending' 
WHEN 'S' THEN 'Satisfied' 
WHEN 'T' THEN 'Settled' 
WHEN '' AND NULLIF(PRDE.SATISFIEDDATE, '') IS NOT NULL THEN 'Satisfied' END AS STATUS