307
Chapter 12: Modifying Data In SQL Server
12
SELECT EmployeeID, Salary,
CAST(CAST(DATEDIFF(d, DateHire, '20120625')
AS DECIMAL(7, 2)) / 365.25 AS INT)
AS YrsCo,
CAST(CAST(DATEDIFF(d, DatePosition, '20120625')
AS DECIMAL(7, 2)) / 365.25
* 12 AS INT)
AS MoPos,
CASE WHEN Employee.PerformanceRating >= 2
THEN Employee.PerformanceRating
ELSE 0
END AS Perf,
Dept.RaiseFactor
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptIDResult:EmployeeID Salary YrsCo MoPos Perf RaiseFactor
----------- ------- ----------- ----------- ------ -----------
1 54000.00 15 185 2.00 1.20
2 78000.00 15 185 0.00 1.20
3 45000.00 13 160 3.50 0.80
4 85000.00 10 120 2.40 0.80
5 120000.00 12 150 4.00 2.50
6 95000.00 14 172 0.00 2.50
7 75000.00 6 78 2.90 1.00
8 34000.00 4 49 3.20 1.00The next step in developing this query is to add the raise calculation. The simplest way to
see the calculation is to pull the values already generated from a subquery:SELECT EmployeeID, Salary,
(2 + ((YearsCompany * .1) + (MonthPosition * .02)
+ (Performance * .5)) * RaiseFactor) / 100 AS EmpRaise
FROM (SELECT EmployeeID, FirstName, LastName, Salary,
CAST(CAST(DATEDIFF(d, DateHire, '20120625') AS
DECIMAL(7, 2)) / 365.25 AS INT) AS YearsCompany,
CAST(CAST(DATEDIFF(d, DatePosition, '20120625') AS
DECIMAL(7, 2)) / 365.25 * 12 AS INT) AS MonthPosition,
CASE WHEN Employee.PerformanceRating >= 2
THEN Employee.PerformanceRating
ELSE 0
END AS Performance, Dept.RaiseFactor
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID) AS SubQueryc12.indd 307c12.indd 307 7/30/2012 4:42:40 PM7/30/2012 4:42:40 PM
http://www.it-ebooks.info