How to use calculated column value to another column in the same SQL Query

I am writing a query, which is using SubQuery to get some result.

Instead of rewriting the SubQuery every time, i want to use the same output value of column 1 as input to another column for the further calculation.

  • I want to calculate time diff between starttime=23:30 and endtime=00:15 the time diff is coming -23.25
  • Does Sql Server cache stored procedure execution plans when calling from c#?
  • Limit SQL query to only scan tables with less than 500 records
  • how to insert/update entity framework entities that are partially mapped to a view?
  • Which DB driver to use DbVisualiser with Azure SQL?
  • Joining a table (or not) based on a condition in SQL
  • Point 1: Can we use any variable to save the value into it and use the same for another column.

    Sample expected code:

      SELECT COLUMN1
            ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END AS **TransactionType**
            ,COLUMN2
            ,CASE **TransactionType** WHEN 'Cancel' THEN EXPR 1.... 
             CASE **TransactionType** WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2     
    FROM TABLE1
    JOIN TABLE2 ....
    

    One Solution collect form web for “How to use calculated column value to another column in the same SQL Query”

    Well, you can’t. There are ways to achieve what you want:

    A.) using subquery

    SELECT Column1, 
           TransactionType,
           CASE TransactionType WHEN 'Cancel' THEN EXPR 1.... 
             CASE TransactionType WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2  
    FROM
    (
    
       SELECT COLUMN1
            ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN   'Cancel' ELSE 'New' END AS **TransactionType**   
       FROM .....
    ) ...
    

    B.) using the expression itself

    SELECT COLUMN1
            ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END AS TransactionType
            ,COLUMN2
            ,CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'Cancel' THEN EXPR 1.... 
             CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2     
    FROM TABLE1
    JOIN TABLE2 ....
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.