Microsoft® SQL Server® 2012 Bible

306

Part II: Building Databases and Working with Data

The sample code sets up the scenario by creating a couple of tables and populating them

with test data:

USE AdventureWorks

CREATE TABLE dbo.Dept (

DeptID INT IDENTITY

NOT NULL

PRIMARY KEY,

DeptName VARCHAR(50) NOT NULL,

RaiseFactor NUMERIC(4, 2)

)

CREATE TABLE dbo.Employee (

EmployeeID INT IDENTITY

NOT NULL

PRIMARY KEY,

DeptID INT FOREIGN KEY REFERENCES Dept,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Salary NUMERIC(9,2) NOT NULL,

PerformanceRating NUMERIC(4,2) NOT NULL,

DateHire DATE NOT NULL,

DatePosition DATE NOT NULL

)

INSERT dbo.Dept (DeptName, RaiseFactor)

VALUES ('Engineering', 1.2),

('Sales', .8),

('IT', 2.5),

('Manufacturing', 1.0) ;

INSERT dbo.Employee (DeptID, LastName, FirstName,

Salary, PerformanceRating, DateHire, DatePosition)

VALUES (1, 'Smith', 'Sam', 54000, 2.0, '19970101', '19970101'),

(1, 'Nelson', 'Slim', 78000, 1.5, '19970101', '19970101'),

(2, 'Ball', 'Sally', 45000, 3.5, '19990202', '19990202'),

(2, 'Kelly', 'Jeff', 85000, 2.4, '20020625', '20020625'),

(3, 'Guelzow', 'Jo', 120000, 4.0, '19991205', '19991205'),

(3, 'Ander', 'Missy', 95000, 1.8, '19980201', '19980201'),

(4, 'Reagan', 'Sam', 75000, 2.9, '20051215', '20051215'),

(4, 'Adams', 'Hank', 34000, 3.2, '20080501', '20080501');

When developing complex queries, work from the inside out. The fi rst step performs

the date math; it selects the data required for the raise calculation, assuming June 25,

2012 is the effective date of the raise, and ensures the performance rating won’t count

if it’s only 1:

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

http://www.it-ebooks.info