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
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