Case statement query How can this be re-written? somehow not able to make it right. In case statement THEN and ELSE are the same
would like to change it to a single statement.
SELECT    t.status_id status_id, (
CASE WHEN t .status_id NOT IN (SELECT     status_id  FROM  firm_return   WHERE      enabled = 'D' and firm_id=t.firm_id) 

THEN (cl.category_code + '-' + s.status_name)   ELSE cl.category_code + '-' + s.status_name END)  AS status_type
   FROM       bla bla
Your example query appears to be incomplete (e.g. your aliases t and cl don't correspond to anything in the example). Ignoring that for a second and using your example query, this is probably what you're after:
SELECT t.status_id AS status_id, cl.category_code + '-' + s.status_name AS status_type
FROM SomeTable AS t
LEFT JOIN firm_return AS s
    ON t.firm_id = s.firm_id
    AND s.enabled = 'D'
... -- The rest of your query logic here (e.g. the table for the cl alias)

If you care about NULLS and want to handle them with a default value then you can use the ISNULL() function around the logic for your status_type column like so: ISNULL(cl.category_code + '-' + s.status_name, 'SomeDefaultValueGoesHere') AS status_type