Thoughts and solutions of attendance problem

Recently in doing an attendance system, attendance is mainly concerned about absence, late and early leave, the current card controller can record the user name and clocking time, the user may clock several times a day, but also may one day only played a card, these situations need to consider. The clocking information is stored in the time sheet, from which you have to dig out people who are absent for one months, who are late and who leave early, and who can display absences, lateness, and early leave.

Attendance table

CREATE TABLE [dbo]. [Kaoqin] (
	[user_name] [varchar] (NULL,
	[card_time] [datetime] null) on
[PRIMARY]

Go

Inserting test data

INSERT into [master]. [dbo]. [Kaoqin]
Select ' John ', ' 2014-08-03 09:36:00 '
UNION ALL
Select ' John ', ' 2014-08-03 18:10:00 '
UNION ALL
Select ' John ', ' 2014-08-04 08:32:00 '
UNION ALL
Select ' John ', ' 2014-08-04 15:15:00 '
UNION ALL
Select ' John ', ' 2014-08-05 09:32:00 '
UNION ALL
Select ' John ', ' 2014-08-05 15:15:00 '
UNION ALL
Select ' John ', ' 2014-08-01 08:36:00 '
UNION ALL
Select ' John ', ' 2014-08-01 18:10:00 '
UNION ALL
Select ' John ', ' 2014-08-02 08:32:00 '
UNION ALL
Select ' John ', ' 2014-08-02 18:15:00 '
UNION ALL
Select ' John ', ' 2014-08-25 08:00:00 '
UNION ALL
Select ' John ', ' 2014-08-24 19:00:00 '
UNION ALL
Select ' John ', ' 2014-08-27 08:00:00 '
Union
All Select ' John ', ' 2014-08-27 17:00:00 '
UNION ALL
Select ' John ', ' 2014-08-26 10:00:00 '
UNION ALL
Select ' John ', ' 2014-08-26 18:30:00 '
UNION ALL
Select ' John ', ' 2014-08-26 8:00:00 '
UNION ALL
Select ' John ', ' 2014-08-27 18:56:00 ' Go
          

My idea is to use a temporary table to get all the working days of the month, to cross connect the temporary table to the user, so that each user has a record on every weekday of the month. Let's say 9 in the morning for work hours and 18 for work hours, which can be used as a form of variable later.

DECLARE @time_start datetime
declare	@time_end datetime 

Set @time_start = ' 2014-08-01 00:00:00 '
set @ Time_end = DATEADD (m,1, @time_start)-

-one-month working day
IF object_id (' tempdb ... #tempDate ') is isn't null
BEGIN
	drop table #tempDate
end
CREATE table #tempDate
(	
	stat_day varchar ()
)

IF object_id (' tempdb.. #tempUserDate ') is isn't null
BEGIN
	drop table #tempUserDate
end
CREATE table #tempUserDate
( C19/>stat_day varchar (m),
	[user_name] varchar ()
)
CREATE clustered index tempuserdate_index1 on # Tempuserdate ([user_name],stat_day)

declare @time_temp datetime
Set @time_temp = @time_start while
@ Time_temp < @time_end
begin
   If DATEPART (weekday, @time_temp) >1 and DATEPART (weekday, @time_temp) <7
   begin
	   INSERT INTO #tempDate (stat_day) VALUES (CONVERT (varchar, @time_temp, 121)) End
   SET @ time_temp= DateAdd (d,1, @time_temp)
end

Insert to #tempUserDate
select * from #tempDate  Cross Join
(SELECT DISTINCT [user_name] from [Kaoqin]) t

From the original Kaoqin table for each user's work time and work time, if the user opened more than two records a day, then will take the earliest and the latest one as work hours and hours.

SELECT [User_name],convert (varchar), 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 (a), card_time,121)

The temporary table #tempuserdate is associated with the query result above, and if the left join is empty, the absence of the person is proven.

--Absence
select * #tempUserDate a left
join
(
    SELECT [User_name],convert (varchar), 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 (), card_time,121)
) b on A.[user_name]=b.[user_name] and A.stat_day=b.stat_day
where [b].[ USER_NAME] is null

Here are the implementation SQL for late and early retirement.

--Late
select * #tempUserDate a left
join
(
    SELECT [User_name],convert (varchar), 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 (), card_time,121)
) b on A.[user_name]=b.[user_name] and A.stat_day=b.stat_day
where CONVERT ( varchar (m), [b]. [On_time], 8 > ' 09:00:00 '-

-Leave early
select * from #tempUserDate a left
join
(
    SELECT [user_name], CONVERT (varchar (), 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, card_time,121)
) b on A.[user_name]=b.[user_name] and A.stat_day =b.stat_day
where CONVERT (varchar), [b].[ Off_time], 8 < ' 18:00:00 '

The results obtained

A person who is late and leaving early today will be reflected in the final results, as can be seen from the 2014-08-05 data. Of course, the attendance system is not perfect, such as not to take into account the holiday, the initial consideration is to use job to store the annual festival regularly, if the staff leave, but also need to be included in the system considerations.