How can I use case information in a new column in SQL?

Question

I am learning SQL from an online tutorial series and in this video we learned how to use case statements.

I understand how to use it, but then I thought about "well can I use the new data from the column I made?" I tried playing around and added in another CASE statement to try and use the UpdatedSalary column, but it wouldn't work. I tried googling a bunch, but I really don't know how to word the question.

SELECT 
    FirstName, LastName, JobTitle, Salary,
    CASE 
        WHEN JobTitle = 'Salesman' THEN Salary + (Salary * .10)
        WHEN JobTitle = 'Accountant' THEN Salary + (Salary * .05)
        WHEN Jobtitle = 'HR' THEN Salary + (Salary * .00001)
        ELSE Salary + (Salary * .03)
    END AS UpdatedSalary
    CASE 
        WHEN UpdatedSalary > 0 THEN UpdatedSalary - Salary 
        ELSE 
    END AS NetIncrease
FROM 
    SQLTutorial.dbo.EmployeeDemographics
JOIN 
    SQLTutorial.dbo.EmployeeSalary ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

Answer 1

You can not use column alias like this if you want to use this you need to put same condition in both the columns or need to use outer query.

SELECT FirstName, LastName, JobTitle, Salary,
CASE 
    WHEN JobTitle = 'Salesman'
    THEN Salary + (Salary * .10)
    WHEN JobTitle = 'Accountant'
    THEN Salary + (Salary * .05)
    WHEN Jobtitle = 'HR' 
    THEN Salary + (Salary * .00001)
    ELSE Salary + (Salary * .03)
END
AS UpdatedSalary,

CASE 
    WHEN (CASE 
    WHEN JobTitle = 'Salesman'
    THEN Salary + (Salary * .10)
    WHEN JobTitle = 'Accountant'
    THEN Salary + (Salary * .05)
    WHEN Jobtitle = 'HR' 
    THEN Salary + (Salary * .00001)
    ELSE Salary + (Salary * .03)
END)> 0
    THEN (CASE 
    WHEN JobTitle = 'Salesman'
    THEN Salary + (Salary * .10)
    WHEN JobTitle = 'Accountant'
    THEN Salary + (Salary * .05)
    WHEN Jobtitle = 'HR' 
    THEN Salary + (Salary * .00001)
    ELSE Salary + (Salary * .03)
END)- Salary 
    ELSE
END
AS NetIncrease

FROM SQLTutorial.dbo.EmployeeDemographics
JOIN SQLTutorial.dbo.EmployeeSalary
    ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID

Or

SELECT FirstName, LastName, JobTitle, Salary,UpdatedSalary,
CASE 
    WHEN UpdatedSalary > 0
    THEN UpdatedSalary - Salary 
    ELSE
END
AS NetIncrease

from (
SELECT FirstName, LastName, JobTitle, Salary,
CASE 
    WHEN JobTitle = 'Salesman'
    THEN Salary + (Salary * .10)
    WHEN JobTitle = 'Accountant'
    THEN Salary + (Salary * .05)
    WHEN Jobtitle = 'HR' 
    THEN Salary + (Salary * .00001)
    ELSE Salary + (Salary * .03)
END
AS UpdatedSalary


FROM SQLTutorial.dbo.EmployeeDemographics
JOIN SQLTutorial.dbo.EmployeeSalary
    ON EmployeeDemographics.EmployeeID = EmployeeSalary.EmployeeID)t