Little confuse, Are you Extracting data from some source and then using derived column transformation to implement above expression?
Or you are using Execute SQL Task?
Can you please let us know the senario with little details so you can get better help.
If you are in Data flow and you are extracting data from SQL server then you can just write this query in OLEDB Source , by using CASE statement.
If you are in Data flow task and then your source is flat file or execel file then you need to use derived column to implement this logic.
Please let us know the details.