Ask the Expert

Show names of frequently absent employees

I need to display the names of the employees with their department names that have been out of office for more then six times in the last six months. Can you help?

    Requires Free Membership to View

I'd love to take this opportunity to present a new breakthrough technique that will make this a breeze…but there is really nothing new in SQL Server 2005 that will help solve this problem. So I'll instead use this answer as a reminder to readers -- there's no substitute for the basics, and T-SQL is just as important in SQL Server 2005 as it has been in previous versions!

Assume that we have the following tables of Departments, Employee, and EmployeeSickDays (schema somewhat abridged for brevity):

CREATE TABLE dbo.Departments

CREATE TABLE dbo.Employees
 DepartmentId INT NOT NULL
  REFERENCES dbo.Departments (DepartmentId),
 EmployeeName VARCHAR(50) NOT NULL

CREATE TABLE dbo.EmployeeSickDays
 EmployeeId INT NOT NULL
  REFERENCES dbo.Employees (EmployeeId),
 CONSTRAINT PK_EmployeeSickDays 
PRIMARY KEY (EmployeeId, SickDate),
  CHECK (SickDate = DATEADD(dd, 0, DATEDIFF(dd, 0, SickDate)))

These table definitions are relatively straightforward: A table of departments, each with a unique surrogate identifier. A table of employees, each row of which is related to a department, forming the one-to-many relationship between departments and employees (a department can have many employees; an employee is a member of exactly one department). And finally, a table of employee sick days, with a composite primary key consisting of the employee's surrogate identifier and the sick date.

It's important to notice the CHECK constraint, CK_SickDate_Day, which ensures that any date in the SickDate column will have a time of 00:00:00. This is especially important because if dates were allowed to have other time portions, an employee could be marked sick multiple times on the same day! Remember that, even with the best-written queries, your data will only ever be as good as the constraints that maintain its integrity.

The challenge -- now that we've ensured quality data -- is to write the query that will return the names and departments of employees who've been out of the office more than six times in the last six months. Luckily, this task is also fairly straightforward. The following query does the job:

 COUNT(*) AS NumberDaysOut
FROM dbo.Departments D
JOIN dbo.Employees E ON E.DepartmentId = D.DepartmentId
JOIN dbo.EmployeeSickDays ES ON ES.EmployeeId = E.EmployeeId
 ES.SickDate >= DATEADD(mm, -6, GETDATE())

So how does this query work? Starting in the FROM clause: Each table is joined based on foreign keys that were set up as per the DDL listed above. Departments to Employees based on the DepartmentId, and Employees to EmployeeSickDays based on the EmployeeId. Next, to the WHERE clause, where we filter the SickDate column of the EmployeeSickDays table, making sure to only return dates that occur after the current date minus six months (i.e., those dates within the last six months.)

The results are grouped by the EmployeeId, EmployeeName and DepartmentName -- which means that for each distinct combination of values found in the results, an aggregation will occur. In this case, that aggregation is a COUNT of rows -- this will count the number of rows in the EmployeeSickDays table for each employee and department, that have rows with dates in the last six months. Note that although EmployeeId does not appear in the SELECT list, it's still being used for grouping. Why? Insurance! What if there are two employees named John Smith in the Sales department?

Finally, the HAVING clause checks to make sure that at least six rows were counted for each aggregation, so the query won't return rows for employees who are under the threshold.

This query will work fine in older versions of SQL Server, as well as in SQL Server 2005. There are no new tricks in SQL Server 2005 for situations like these. The best trick in any developer's toolkit is always having a solid understanding of foundations!

Do you have comments on this Ask the Expert Q&A? Let us know.

This was first published in May 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: