Data Management

Two approaches to recording shift work in SQL Server

If you want to list employees' time sheet records in SQL Server by the shift, you should assume that an employee could begin work at any time between the start and stop times of the shift. Learn two ways to preserve the correct groupings in your list.

Assume that your organization has three shifts for its factory workers: 12:00 A.M. to 8:00 A.M., 8:00 A.M. to 4:00 P.M., and 4:00 P.M. to 12:00 A.M. Employees sign in using an electronic clock, which automatically adds records to the SQL Server database. From time to time, your organization adds a graveyard shift when necessary; even though this shift occurs on the following day, it is still regarded as the third shift.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

You want to list the records grouped by the shift, but there are two issues that may put a wrinkle in your plans: (1) not all employees arrive for work on time and (2) sometimes employees have to work longer than their shift. To preserve the correct groupings in your list, you have to assume that any employee could begin work at any time between the start and stop times of the shift.

One way to handle this is to use a CASE statement. The SQL statement in Listing A shows how this might work, without the hassle of creating a test table.

For this code to be completely accurate, you need to factor in the small window of time between the end of each shift and the beginning of the next one. In the previous example, substitute the value '15:59:59.123' for @StartTime and rerun the code. Now you must add 999 milliseconds to each shift-end boundary.

Refining the solution

This example assumes that the shift times will never change, but what if they do? You would have to revise any code that is similar to what is in the example in light of the changed times.

A better idea is to create a table called Shifts to house the start and stop times for our shifts. You can modify the Case statement to look at the StopTime column in this table.

Listing B contains the code to create and populate the Shifts table. The code to place a time sheet into a shift is now both simple and flexible:

SET @StartTime ='22:59:59'

SELECTTOP 1 ShiftName FROM ShiftWork.Shifts
      WHERE Shifts.StartTime <= @StartTime     

The reason that I put the shift rows in descending order is to get around the problem of the boundaries of the third shift. I'll verify my results with a few test cases, which you can see in Listing C.

In practice, you would apply this logic to rows in a TimeCards table rather than create another table and populate it. I used a simple variable to verify my logic.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

Editor's Picks

Free Newsletters, In your Inbox