Train of Thought and solution for attendance Issues, and train of thought for attendance Issues

Train of Thought and solution for attendance Issues, and train of thought for attendance Issues

Recently, I am working on an attendance system. Attendance mainly focuses on absenteeism, late arrival, and early departure. Currently, the punching controller can record the user name and the time of punching in. Users may punch in multiple times a day, it is also possible that only one card is punched in once a day, which must be considered. The punching information is stored in the attendance sheet, from which you need to find out the number of people who are absent, late, and left early within one month. It also shows the time of absence, late, and leave.

Attendance table

CREATE TABLE [dbo].[kaoqin]([user_name] [varchar](50) NULL,[card_time] [datetime] NULL) ON [PRIMARY]GO

Insert Test Data

Insert into [master]. [dbo]. [kaoqin] select 'zhang san', '2017-08-03 09:36:00 'union allselect 'zhang san', '2017-08-03 18:10:00' union allselect 'zhang san ', '2014-08-04 08:32:00 'union allselect 'zhang san', '2014-08-04 15:15:00' union allselect 'zhang san ', '2014-08-05 09:32:00 'union allselect 'zhang san', '2014-08-05 15:15:00' union allselect 'zhang san ', '2014-08-01 08:36:00 'union allselect 'zhang san', '2014-08-01 18:10:00' union allselect 'zhang san ', '2014-08-02 08:32:00 'union allselect 'zhang san', '2014-08-02 18:15:00' union allselect 'zhang san ', '2014-08-25 08:00:00 'union allselect 'zhang san', '2014-08-24 19:00:00' union allselect 'zhang san ', '2014-08-27 08:00:00 'union allselect 'zhang san', '2014-08-27 17:00:00' union allselect 'zhang san ', '2014-08-26 10:00:00 'union allselect 'zhang san', '2014-08-26 18:30:00' union allselect 'zhang san ', '2017-08-26 8:00:00 'union allselect 'zhang san', '2017-08-27 18:56:00' GO

My idea is to use a temporary table to get all the workdays of the month and cross-connect the temporary table with the user. In this way, each user has a record every workday of the month. Assume that am is the start time, and am is the end time. This can be used as a variable later.

Declare @ time_start datetimedeclare @ time_end datetime set @ time_start = '2017-08-01 00:00:00 'set @ time_end = DATEADD (M, 1, @ time_start) -- IF object_id ('tempdb .. # tempdate') is not nullBEGINdrop table # tempDateENDCREATE table # tempDate (stat_day varchar (10) IF object_id ('tempdb .. # tempUserDate ') is not nullBEGINdrop table # tempUserDateENDCREATE table # tempUserDate (stat_day varchar (10), [user_name] varchar (40) CREATE clustered index partition on # tempUserDate ([user_name], stat_day) declare @ time_temp datetimeset @ time_temp = @ time_startwhile @ time_temp <@ time_endbegin if datepart (weekday, @ time_temp)> 1 and datepart (weekday, @ time_temp) <7 begin insert into # tempDate (stat_day) values (CONVERT (varchar (10), @ time_temp, 121) end set @ time_temp = dateadd (d, 1, @ time_temp) endinsert into # tempUserDateselect * from # tempDate cross join (select distinct [user_name] from [kaoqin]) t

Query the start time and end time of each user from the original kaoqin table. If the user opens more than two records each day, then, the earliest and last lines are used as the start time and end time respectively.

select [user_name],CONVERT(varchar(10),card_time,121) as stat_day,    MIN(card_time) as on_time,MAX(card_time) as off_time from [kaoqin]    group by [user_name],CONVERT(varchar(10),card_time,121)

Associate the temporary table # tempUserDate with the preceding query result. If the left join is empty, the employee is absent.

-- Select * from # tempUserDate aleft join (select [user_name], CONVERT (varchar (10), card_time, 121) as stat_day, MIN (card_time) as on_time, MAX (card_time) as off_time from [kaoqin] group by [user_name], CONVERT (varchar (10), card_time, 121) B on. [user_name] = B. [user_name] and. stat_day = B. stat_daywhere [B]. [user_name] is null

The following is an SQL statement for arriving late and leaving early.

-- Select * from # tempUserDate aleft join (select [user_name], CONVERT (varchar (10), card_time, 121) as stat_day, MIN (card_time) as on_time, MAX (card_time) as off_time from [kaoqin] group by [user_name], CONVERT (varchar (10), card_time, 121) B on. [user_name] = B. [user_name] and. stat_day = B. stat_daywhere CONVERT (varchar (100), [B]. [on_time], 8)> '09: 00: 00' -- early return select * from # tempUserDate aleft join (select [user_name], CONVERT (varchar (10), card_time, 121) as stat_day, MIN (card_time) as on_time, MAX (card_time) as off_time from [kaoqin] group by [user_name], CONVERT (varchar (10), card_time, 121 )) B on. [user_name] = B. [user_name] and. stat_day = B. stat_daywhere CONVERT (varchar (100), [B]. [off_time], 8) <'18: 00: 00'

Result

If a person is late and leaves early today, it will be reflected in the final result, which can be seen from. Of course, this attendance system is not complete yet. For example, we did not consider festivals. The initial consideration is to use jobs to regularly store annual holidays. If employees ask for leave, it also needs to be included in system considerations.

My Attendance Machine also encountered a time error and could not analyze the data. How can this problem be solved?

Does LZ solve the problem by referring to the solution I told netizens last time? He has done a good job. The problem is that there is no data at the root of the database, and a time error occurs during analysis, and then the data is imported from the attendance machine. It helped him deal with the problem for more than an hour. Hey. Then, we got it done.

Attendance Management Problems

Method 1: The system is late and punched out, and a table is placed at the door. If a signature is signed late, two persons are on duty to supervise the signature.
Method 2: office software management. If no one has a computer, the system can record the time of his first logon on the same day.
Method 3: severe fraud.