sql query to get the column value again in case statement

select *, 
case when number = '12' and status = 'y' then cost
end as [price]
from tblx

i got the results from the above query, i want to use the value of price column, again in case statement of the same query as like below

select *, 
case when number = '12' and status = 'y' then cost-500
end as [price],
case when price = 24 then trasdate end as [trasdate]
from tblx

If my question is not clear, please suggest me

I am working in a stored procedure with more than two joins connecting 8 tables, i just want to get the column value again to use it on case statement on the same query, I shortened the question as the query is bigger.

Thanks

In the same query you need to just duplicate the same case statement where you've attempted to reference it. Btw you should probably add an else 0 to it because currently it'll return null if your conditions don't evaluate to true which could affect its usage in the other case statement.

There are other options if you don't want to duplicate the case statement e.g.:

• use the price statement in a sub query and reference it by name in an outer query
• use a temp table with price added as a calculated column
• as above but use a table variable

Just repeating the statement is obviously simplest and may well perform best too, downside is duplication. In terms of performance, test for your likely use case.