TAILIEUCHUNG - Hướng dẫn học Microsoft SQL Server 2008 part 41

Manipulating Data With Select DatePosition DATE NOT NULL ) INSERT (DeptName, RaiseFactor) VALUES (’Engineering’, ), (’Sales’, .8), (’IT’, ), (’Manufacturing’, ) ; INSERT (DeptID, LastName, FirstName, Salary, PerformanceRating, DateHire, DatePosition) VALUES (1, ‘Smith’, ‘Sam’, 54000, , ‘19970101’, ‘19970101’), | Part II Manipulating Data With Select DatePosition DATE NOT NULL INSERT DeptName RaiseFactor VALUES Engineering Sales .8 IT Manufacturing INSERT DeptID LastName FirstName Salary PerformanceRating DateHire DatePosition VALUES 1 Smith Sam 54000 2 .0 19970101 19970101 1 Nelson Slim 78000 19970101 19970101 2 Ball Sally 45000 19990202 19990202 2 Kelly Jeff 85000 20020625 20020625 3 Guelzow Jo 120000 19991205 19991205 3 Ander Missy 95000 19980201 19980201 4 Reagan Sam 75000 20051215 20051215 4 Adams Hank 34000 20080501 20080501 When developing complex queries I work from the inside out. The first step performs the date math it selects the data required for the raise calculation assuming June 25 2009 is the effective date of the raise and ensures the performance rating won t count if it s only 1 SELECT EmployeelD Salary CASTCCASTCDATEDIFFCd DateHire 20090625 AS DECIMALC7 2 I AS INT AS YrsCo CASTCCASTCDATEDIFFCd DatePosition 20090625 AS DECIMALC7 2 I 12 AS INT AS MoPos CASE WHEN 2 THEN ELSE 0 END AS Perf FROM JOIN ON Result EmployeeID Salary YrsCo MoPos Perf RaiseFactor 1 12--- 149 2 12 149 3 10 124 362 Modifying Data 15 4 7 84 5 9 114 6 11 136 7 4 42 8 1 13 The 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 EmployeelD Salary 2 YearsCompany .1 MonthPosition .02 Performance .5 RaiseFactor 100 AS EmpRaise FROM SELECT EmployeelD FirstName LastName Salary CAST CAST DATEDIFF d DateHire 20090625 AS DECIMAL 7 2 AS INT AS YearsCompany CAST CAST DATEDIFF d DatePosition 20090625 AS DECIMAL

• Cơ sở dữ liệu
• Giáo trình SQL Server 2008
• Microsoft SQL Server 2008
• quản trị dữ liệu
• cài đặt SQL Server 2008
• công cụ của SQL Server 2008
• Giáo trình Microsoft SQL Server 2008
• Quản trị cơ sở dữ liệu
• Cài đặt Microsoft SQL Server 2008
• Cấu trúc trình đơn của Microsoft SQL Server 2008
• Cơ sở dữ liệu SQL Server 2008
• Dịch vụ Report trong SQL Server 2008
• SQL Server Business Intelligence Development Studio
• Bảo mật cơ sở dữ liệu SQL Server 2008
• SQL Server 2008
• Cơ bản SQL Server 2008
• Kiến trúc SQL Server 2008
• Phục hồi cơ sở dữ liệu