Teach you easy to learn SQL Server record shift skills _mssql

Example: The company staff to take three shift system: 0:00 to the morning 8:00 for the first class, 8:00 to 4:00 for the second shift, 4:00 to the evening 12:00 for the third class.

The employee signs with an electronic clock that automatically adds records to the SQL Server database. Sometimes, however, a night shift is required; even if the shift takes place the next day, it will still be considered the third shift.

You may want to group records according to shifts, but there are two very troubling questions in your plan:
(1) Not all employees arrive on time;
(2) Sometimes employees work longer hours than they do on one shift.

To get the right grouping in your list, you have to assume that all employees can start working at any time between the start and end times of a shift.

Using Case statements is a solution. The SQL statement in Listing a tells us how it works when the test table is created without errors.

In order for the program code to run exactly, you need to get the start time of each shift and the end of the next shift in the small window of time. In the example above, you can use the value "15:59:59.123" instead of @starttime, and then run the code again. At this point, you have to add 999 milliseconds to the dividing line at the end of each shift.

Solution

This example assumes that shift time has never changed, but what if you change the shift time? You have to follow this example and write all the code based on the changed time.

A better idea would be to create a table for our shifts to invoke the start and end times of the shifts. In this table, you can modify the case statement to find the StopTime column.

Listing B contains code that creates a shifts table and adds records to the table. After the code has been set in one shift, this makes the code look both simple and flexible:

Copy Code code as follows:

DECLARE @StartTime VARCHAR (20)
SET @StartTime = "22:59:59"
Selecttop 1 shiftname from Shiftwork.shifts
WHERE shifts.starttime <= @StartTime
by StartTime DESC

The reason I want to keep shift records in descending order is to avoid the boundary problems of a third shift. I will use some test cases to illustrate my conclusions, which you can see in Listing C.

In fact, you can apply this logic to the records in the Temecards table without having to create additional tables, and you don't need to add data to the table. I also use a simple variable to justify my logic.