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.
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.