Microsoft® SQL Server® 2012 Bible

308

Part II: Building Databases and Working with Data

Result:

EmployeeID Salary EmpRaise

----------- ------------ ---------------

1 54000.00 0.082160000

2 78000.00 0.070160000

3 45000.00 0.061840000

4 85000.00 0.048640000

5 120000.00 0.149500000

6 95000.00 0.115500000

7 75000.00 0.046900000

8 34000.00 0.039600000

The last query was relatively easy to read, but there’s no logical reason for the subquery.

The query could be rewritten combining the date calculations and the case expression into

the raise formula:

SELECT EmployeeID, Salary,

(2 +

-- years with company

 + ((CAST(CAST(DATEDIFF(d, DateHire, '20120625')

AS DECIMAL(7, 2)) / 365.25 AS INT) * .1)

-- months in position

 + (CAST(CAST(DATEDIFF(d, DatePosition, '20120625')

AS DECIMAL(7, 2)) / 365.25 * 12 AS INT) * .02)

-- Performance Rating minimum

 + (CASE WHEN Employee.PerformanceRating >= 2

THEN Employee.PerformanceRating

ELSE 0

END * .5))

-- Raise Factor

* RaiseFactor) / 100 AS EmpRaise

FROM dbo.Employee

JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

It’s easy to verify that this query gets the same result, but which is the better query? From

a performance perspective, both queries generate the exact same query execution plan.

When considering maintenance and readability, you should probably go with the second

query carefully formatted and commented.

The fi nal step is to convert the query into an UPDATE command. The hard part is already

done — it just needs the UPDATE verb at the front of the query:

UPDATE Employee

SET Salary = Salary *

(1 + ((2

-- years with company

 + ((CAST(CAST(DATEDIFF(d, DateHire, '20120625')

c12.indd 308c12.indd 308 7/30/2012 4:42:40 PM7/30/2012 4:42:40 PM

http://www.it-ebooks.info