请高手解决关于考勤统计时间方面的问题，不解决就有可能被炒了，谢谢高手 谢谢（分不够改天加）

Question

     考勤表里面对应的格式是 ：ID号（自动编号）  人员卡号     考勤时间 
比如说   1  2038375602   2010-03-08 9:21:05   
         2  2038375602   2010-03-08 14:25:05   
         3  2038375602   2010-03-08 14:29:05
         4  2038375602   2010-03-08 17:30:05
这种情况就是中途出去了一次 又进来了  
         1  2038375602   2010-03-08 9:21:05   
         2  2038375602   2010-03-08 14:25:05   
         3  2038375602   2010-03-08 14:29:05
这种情况可能是中途出去了 进来了 后回去忘了刷卡         1  2038375602   2010-03-08 9:21:05   
         2  2038375602   2010-03-08 14:25:05   
         3  2038375602   2010-03-08 14:29:05
         4  2038375602   2010-03-08 15:30:05
         5  2038375602   2010-03-08 16:29:05
         6  2038375602   2010-03-08 17:30:05
这种情况是中途出去两次了 。希望有做过考勤方面的高手能帮我 解决一下  这样的统计 每天上班多长时间的Sql语句怎么写  非常谢谢
  

Answer 1

   谢谢高手们的解答，那如果正常中途出去又进来 中间的时间总归是要扣去的，那这样的sql 语句应该怎么写？？？    如果是一天奇数次进出又该怎么统计？？？ 也就是说 刷了1次  3次 或 5次 .....

Answer 2

You need a clear set of business rule dealing with this sort of situations and it shouldn't be up to you to make that decision.However, if you must, here is what I would do:(1) Entry without prior exit record
If there is an entry record for a particular shift (again, you need business logic to determine which shift an entry/exit belongs to. Typically, you look at the closest shift start/end time and assign the entry/exit record to it) but no prior entry, you need to determine if it is the first entry record. If it is, then that's the first time the person has started work.If there is a prior entry record but no exit record for the same shift, then you assign a system generated exit record at the same time (or -1 second prior - again this is a value you need to obtain from the relevant process owner or there should be relevant business logic for it).(2) Exit without entry record
Now with exit record but no prior entry record, you employ the same methodology, except in reverse. If this is the first exit record for the shift but no prior entry record exists, you again generate a system generated record for record the entry (could be that you count the entry time for the start of the shift, or one second prior to the exit time - again, you will need to get business process owner involved in making such a decision). If there is prior exit record but no entry record, then again you could create a system generated entry record (that is 1 sec prior to the exit record or some other period).You could make the various options parameterised (such as the amount of time increment/decrement to use to generate system entry/exit record based, and whether the system generated entry/exit record should be at the start of the shift or only 1 second prior to the user generated exit/entry record).

Answer 3

谢谢  csw200201   能把具体的Sql语句写出来吗 ？？？非常期待高手写出Sql 语句

Answer 4

先说明一下，你用的是不是access？
access就根本没法做那么复杂的查询

Answer 5

不是 Access   用的 是 Sql server

Answer 6

晕，你怎么还在纠缠在SQL语句上，换个角度考虑问题。要不然永远是无解的。

Answer 7

good English ~I like it\~

Answer 8

I am in meeting now. I will give you a solution when I am done with my meeting.

Answer 9

Be warned - little to no testing was done. I spent 5 minutes hacking the code to demonstrate how I can calculate total time worked per staff with various assumptions made (such as ther is a shift end time, and automatically set exit time to be the end of the shift if no exit time defected). This code is supposed to give you the idea how to implement sign-in/sign-out routine. I can't claim to resolve your problem because I don't know the business rules you are trying to implement.Hope it helps:
create function dbo.ConvertToDateTime (@indate datetime, @intime varchar(50)) returns datetime as
begin
--this function takes the day part of indate and concatenate intime which is of time format
--and forms a datetime value
return convert(datetime, convert(varchar(10), year(@indate))+'-'+convert(varchar(10), month(@indate))+'-' + convert(varchar(10), day(@indate))+ ' ' +@intime)
end/*
drop table SignInOutData
drop table #entryexit
drop table #EntryExitTime
*/declare @shiftendtime varchar(10) 
--this defines the closing time of the shift. I am only assuming 1 shift for the purpose of this exericseset @shiftendtime='21:00PM'create table SignInOutData
(
recid int identity(1, 1),
employeeid int,
recorddate datetime
)set identity_insert signinoutdata oninsert into SignInOutData (recid, employeeid, recorddate) values (1, 2038375602, '2010-03-08 9:21:05')
insert into SignInOutData (recid, employeeid, recorddate) values (2, 2038375602, '2010-03-08 14:25:05')
insert into SignInOutData (recid, employeeid, recorddate) values (3, 2038375602, '2010-03-08 14:29:05')
insert into SignInOutData (recid, employeeid, recorddate) values (4, 2038375602, '2010-03-08 17:30:05')set identity_insert signinoutdata offcreate table #entryexit
(
RecID int,
EmployeeID int,
RecordDate datetime,
EntryExitType bit --entry = 0 exit = 1
)
;with EntryExit(RecID, EmployeeID, RecordDate, RowNumber) as
(
select
recid, employeeid, recorddate, row_number() over(partition by employeeid, day(recorddate) order by recorddate) 
from
SignInOutData
)insert into #entryexit
select
recid, employeeid, recorddate, (case when (rownumber+1)%2=0 then 0 else 1 end)
from
EntryExitselect
recid, 
employeeid, 
recorddate entry_time, 
(
select
top 1 recorddate
from
#entryexit
where
employeeid=a.employeeid and
day(recorddate)=day(a.recorddate) and
entryexittype=1 and
recid>a.recid
order by
recid
) exit_time
into
#EntryExitTime
from
#entryexit a
where
entryexittype=0update
#EntryExitTime
set
Exit_Time=case
when entry_time>dbo.ConvertToDateTime(entry_time, @shiftendtime) then entry_time
else dbo.ConvertToDateTime(entry_time, @shiftendtime)
end
where
exit_time is nullselect
employeeid, day(entry_time) entry_day, sum(datediff(mi,entry_time, exit_time)) total_working_time_in_minutes
from
#EntryExitTime
group by
employeeid, day(entry_time)

Answer 10

lz看看是否符合要求
---1
if object_id('tb') is not null drop table tb
go
create table tb(id int identity(1,1),人员卡号 varchar(20), 考勤时间 datetime )---2
insert tb
select '2038375602',' 2010-03-08 9:21:05' union all   
select '2038375602',' 2010-03-08 14:25:05' union all      
select '2038375602',' 2010-03-08 14:29:05' union all   
select '2038375602',' 2010-03-08 17:30:05'select rowid=count(1),a.人员卡号 , a.考勤时间  
into #t
from tb a left join tb b 
on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间
group by a.人员卡号 , a.考勤时间
order by a.人员卡号 , a.考勤时间select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！'else '' end,
工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60
from #t a left join  #t b  
on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1
group by a.人员卡号/*人员卡号                                                  工作时间                 
----------------------------------------------------- -------------------- 
2038375602                                            8.016666（所影响的行数为 1 行）*/---3
truncate table tb
go
insert tb
select '2038375602',' 2010-03-08 9:21:05' union all   
select '2038375602',' 2010-03-08 14:25:05' union all      
select '2038375602',' 2010-03-08 14:29:05' 
go
drop table #t
goselect rowid=count(1),a.人员卡号 , a.考勤时间  
into #t
from tb a left join tb b 
on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间
group by a.人员卡号 , a.考勤时间
order by a.人员卡号 , a.考勤时间select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！'else ''end,
工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60
from #t a left join  #t b  
on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1
group by a.人员卡号/*人员卡号                                                  工作时间                 
----------------------------------------------------- -------------------- 
2038375602该卡刷3次,请查明问题！                                5.000000（所影响的行数为 1 行）*/
---4
truncate table tb
go
insert tb
select '2038375602',' 2010-03-08 9:21:05' union all   
select '2038375602',' 2010-03-08 14:25:05' union all      
select '2038375602',' 2010-03-08 14:29:05' union all 
select '2038375602',' 2010-03-08 15:30:05' union all 
select '2038375602',' 2010-03-08 16:29:05' union all   
select '2038375602',' 2010-03-08 17:30:05'
go
drop table #t
goselect rowid=count(1),a.人员卡号 , a.考勤时间  
into #t
from tb a left join tb b 
on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间
group by a.人员卡号 , a.考勤时间
order by a.人员卡号 , a.考勤时间select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题！' else ''end,
工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60
from #t a left join  #t b  
on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1
group by a.人员卡号
/*
人员卡号                                                  工作时间                 
----------------------------------------------------- -------------------- 
2038375602                                            6.050000（所影响的行数为 1 行）
*/

Answer 11

Look at the query I wrote. As you didn't specify there is an entry exit indicator, I had to create one based on the first record being entry, next one being exit, and the one after being entry again.Since you already have that indicator, you can pair one record with the next one. If entry record doesn't pair up with an exit record, then you know there is a missing exit record. You simply need a business rule to determine how you are going to deal with it. Again, look at how I dealt with missing record in the original script.

Answer 12

   非常感谢各位高手的解答，感谢csw200201 这么细致的回答。 
    如果加上 进出 这个字段  出现不规则的 刷卡记录就非常难处理 这个真的是不好做
    比如说  有两个进的  没有出的  有两个进的 一个出的  等等 就要考虑很多情况了 正常的 情况 一进一出  是很好处理的  高手能给出一个非常好的方法吗 ？ 非常谢谢

Answer 13

It works the same way - it's all about what assumptions you make when dealing with inapprpriate set of data - this is the business logic that I keep harping on about.Anyway, I revamped my code based on your revised data structure. Again, I made certain assumptions to deal with missing records - that may not be appropriate in your case. However, I hope you can see for yourself the type of processing that can be done to deal with such issues. It is not so much a SQL problem. You have a business modelling case.
drop table signinoutdata
drop function dbo.ConvertToDateTime
drop table #signinoutdata
gocreate function dbo.ConvertToDateTime (@indate datetime, @intime varchar(50)) returns datetime as
begin
    --this function takes the day part of indate and concatenate intime which is of time format
    --and forms a datetime value
    return convert(datetime, convert(varchar(10), year(@indate))+'-'+convert(varchar(10), month(@indate))+'-' + convert(varchar(10), day(@indate))+ ' ' +@intime)
end
go
declare @shiftendtime varchar(10) 
--this defines the closing time of the shift. I am only assuming 1 shift for the purpose of this exericseset @shiftendtime='21:00PM'create table SignInOutData
(
    recid int identity(1, 1),
    employeeid int,
    recorddate datetime,
recordtype tinyint --1 = entry, 2 = exit
)
set identity_insert signinoutdata on
/*
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (4, 2038375602, '2010-03-08 17:30:05', 2)
*//*
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)   
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)
*/insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (1, 2038375602, '2010-03-08 9:21:05', 1)   
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (2, 2038375602, '2010-03-08 14:25:05', 2)   
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (3, 2038375602, '2010-03-08 14:29:05', 1)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (4, 2038375602, '2010-03-08 15:30:05', 2)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (5, 2038375602, '2010-03-08 16:29:05', 1)
insert into SignInOutData (recid, employeeid, recorddate, recordtype) values (6, 2038375602, '2010-03-08 17:30:05', 2)  set identity_insert signinoutdata offcreate table #signinoutdata
(
signinrecid int,
employeeid int,
signintime datetime,
signoutrecid int,
signouttime datetime
)insert into  #signinoutdata
select
a.recid signinrecid,
a.employeeid,
a.recorddate signintime,
(
select
recid
from
signinoutdata
where
employeeid=a.employeeid and
day(recorddate)=day(a.recorddate) and
recordtype=2 and
recid>a.recid and
recid<(
select 
min(recid) 
from 
signinoutdata 
where 
employeeid=a.employeeid and
day(recorddate)=day(a.recorddate) and
recordtype=1 and
recid>a.recid
)
) signoutrecid,
(
select
recorddate
from
signinoutdata
where
employeeid=a.employeeid and
day(recorddate)=day(a.recorddate) and
recordtype=2 and
recid>a.recid and
recid<(
select 
min(recid) 
from 
signinoutdata 
where 
employeeid=a.employeeid and
day(recorddate)=day(a.recorddate) and
recordtype=1 and
recid>a.recid
)
) signouttime
from
signinoutdata a
where
a.recordtype=1insert into #signinoutdata
select
case when a.recordtype=1 then a.recid else null end,
a.employeeid,
case when a.recordtype=1 then a.recorddate else null end,
case when a.recordtype=2 then a.recid else null end,
case when a.recordtype=2 then a.recorddate else null end
from
SignInOutData a left join
(
select
signinrecid recid
from
#signinoutdata
union
select
signoutrecid
from
#signinoutdata
) b on
a.recid=b.recid
where
b.recid is nullupdate
#signinoutdata
set
signintime=(case when signintime is null then signouttime else signintime end),
signouttime=(case 
when signouttime is not null then signouttime 
when signouttime is null and signintime>dbo.ConvertToDateTime(signintime, @shiftendtime) then signintime
else dbo.ConvertToDateTime(signintime, @shiftendtime)/*signouttime is null and signintime<=dbo.ConvertToDateTime(signintime, @shiftendtime)*/
end)
where
signintime is null or
signouttime is nullselect
employeeid, day(signintime) work_day, sum(datediff(mi, signintime, signouttime)) total_working_time_in_minutes
from
#signinoutdata
group by
employeeid, day(signintime)

Answer 14

   非常感谢csw200201 的回答 耽误您不少时间了吧 非常谢谢  估计是我没有说清楚吧  现在我也考虑把表再简化一下 我把具体情况说一次  （我现在只统计表里面每天为偶数的记录的时间比如下面的记录，也不考虑进出的情况）
  1 2038375602 2010-03-08  9:25:05  
  2 2038375602 2010-03-08 10:25:05    
  3 2038375602 2010-03-08 12:25:05 
  4 2038375602 2010-03-08 15:25:05 
  5 2038375603 2010-03-08 9:00:00    
  6 2038375603 2010-03-08 10:00:00 
  7 2038375603 2010-03-09 9:00:00    
  8 2038375603 2010-03-09 10:30:00   得到的结果 就是      2038375602  2010-03-08  4
                     2038375603  2010-03-08  1
                     2038375602  2010-03-09  1.5
就是统计每天的 考勤小时数