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:

DECLARE @StartTime VARCHAR(20)
SET @StartTime ='22:59:59'

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

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.

8 comments
jcelko212
jcelko212

This approach was too proprietary and procedural for me. SQL Server has never had good temporal data and data support, so casting and converting are messy. I would build a ten-year table of the shifts, with corrections for holidays, etc. and then use BETWEEN predicates to identify the shifts. I could gain the advantage of parallelism in my joins, too.

djj55
djj55

What if someone clocks in before the start of the shift? Would this not put them in the wrong shift? Track a person by assigned shift MIGHT make a better solution. It depends on how often the shift changes for a given employee.

lfschauer
lfschauer

Tables are the way to go, then if something needs to be changed, the table can be changed. Or the same system setup for different companies/divisions with different shifts or pay rules.

john.bruette
john.bruette

My father worked in a paper mill. It was common curtesy to clock in 15 minutes early to releive the person on the previous shift to give them time to cleanup before going home. (Making waxed paper is not a pretty or clean job). The job was shift work as described in the scenario and shifts rotated on a weekly basis.

lfschauer
lfschauer

You are very correct about employees clocking in before their shift. There must be code to handle early/late clock in/out situations. In general I would rather see a table which can be changed easier than the code, as businesses change work schedules. The table could also specify an amount of time to clock early/late before they get overtime or are late.

al
al

To assign shifts, and therefore shift pay multipliers, I find the middle of the shift and then use that to find the shift that they worked. Using the example of 00:00-07:59 (morning), 08:00-15:59 (day), and 16:00 - 23:59 (evening) someone working from 14:10-22:25 would have a middle time of 18:17 putting them squarely into the evening shift (and pulling down some extra pay for that shift.) Company policy is to be paid at the rate of the shift that you primarily worked, so even someone working 12 hours (say noon till midnight) would still get their correct shift as the mid point is 18:00 - again in the evening shift. This works great for multiple swing shifters (a day on one shift, another on one of the others, and a couple of days on the third). No set shift is put into the database, just the results from what they actually worked.

djj55
djj55

If the company rules do not handle it then the mid-point would be an ideal way. Even though I am in IT I still punch a clock and routinly clock 30 minutes early. (Overtime is overtime). djj

lfschauer
lfschauer

What I've found is that some of the business rules must be coded into the program. At times where a union is involved, contract changes can mean program changes also.