SQL Server Magazine

The DATEDIFF function takes three parameters. You use the first parameter to specify the time period being tracked. In this case, you need to specify m for months. You use the second and third parameters to specify the start and end dates, respectively. In this case, the start date is the value in the Employee table’s HireDate column and the end date is the current date, which you obtain with the GETDATE function. So, the query is

SELECT MIN(DateDiff
  (m, HireDate, GETDATE()))
  AS ‘Number of Months’
  FROM Employee

If you run this query on, say, May 20, 2008, the result is 26 months.

To learn more about the MIN, DATEDIFF, GETDATE, or any of the other functions discussed here, highlight the function in your query window and press Shift+F1. This will invoke SQL Server Books Online (BOL) context-sensitive help, which will bring you to the appropriate documentation.

Determining Maximum Values
You can use the MAX function in a SELECT statement to obtain the maximum value for a specified column or arithmetic expression. For example, the query

SELECT MAX(Salary)
  AS ‘Maximum Salary’
  FROM Employee

reveals that the highest salary in the Employee table is $250,000.

Using the DATEDIFF and GETDATE functions, you can determine how many years the most senior employee has been with the fictitious company. The code is similar to that used to obtain how long the most recently hired employee has been with the company, except that MAX rather than MIN is used and the time-period argument is in years (represented by yy) rather than months. So, the query

SELECT MAX(DATEDIFF
  (yy, HireDate, GETDATE()))
  AS ‘Number of Years’
  FROM Employee

reveals that the most senior employee has been with the company for 18 years.

Determining Average Values
You can use the AVG function in a SELECT statement to obtain the average value for a specified column or arithmetic expression. (The values you’re averaging must be numeric.) For example, to determine the average number of years employees have been with the company, you’d run the query

SELECT AVG(DATEDIFF
  (yy, HireDate, GETDATE()))
  AS ‘Average Years of Service’
  FROM Employee

The result is an average of 5 years. Note that the data type returned by AVG is determined by the type of data being evaluated. In this case, the Salary field is the data type of integer, so the data returned is an integer.

Let’s look at another example for AVG. Suppose you want to find the average salary of all the employees. You’d run the query

SELECT AVG(Salary)
  AS ‘AverageSalary’
  FROM Employee

The result is $90,562. You might be tempted to say that our fictitious company pays a decent average salary, but looking at the minimum ($23,500) and maximum ($250,000) salaries reveals that there’s quite a gap between the two. In such cases, you can use the MIN and MAX functions in a WHERE clause to filter out the top and bottom salaries:

SELECT AVG(Salary)
  AS ‘AdjustedAverageSalary’
  FROM Employee
  WHERE Salary < (SELECT MAX(Salary)
  FROM Employee)
  AND Salary > (SELECT MIN(Salary)
  FROM Employee)

Continue to page 3

Prev. page     1 [2] 3     next page