满分100求一个考勤计算问题～谢谢

Question

-->生成测试数据
 
declare @tb table([EventID] int,[EventTime] Datetime,[EmployeeID] int,[CardNo] nvarchar(11),[DoorID] int,[ControlID] int,[EventType] nvarchar(1),[Modify] nvarchar(1))
Insert @tb
select '3825','2008-4-18 7:54',49,N'00003611167',3,2,N'0',N'0' union all
select '3919','2008-4-18 16:15',49,N'00003611167',3,2,N'0',N'0' union all
select '4018','2008-4-19 8:28',49,N'00003611167',3,2,N'0',N'0' union all
select '4042','2008-4-19 15:04',49,N'00003611167',3,2,N'0',N'0' union all
select '4257','2008-4-21 7:57',49,N'00003611167',3,2,N'0',N'0' union all
select '4322','2008-4-21 15:12',49,N'00003611167',3,2,N'0',N'0' union all
select '4441','2008-4-22 8:03',49,N'00003611167',3,2,N'0',N'0' union all
select '4546','2008-4-22 17:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4588','2008-4-23 8:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4618','2008-4-23 16:07',49,N'00003611167',3,2,N'0',N'0' union all
select '4677','2008-4-24 8:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4725','2008-4-24 19:04',49,N'00003611167',3,2,N'0',N'0' union all
select '4864','2008-4-25 8:29',49,N'00003611167',3,2,N'0',N'0' union all
select '4948','2008-4-25 16:03',49,N'00003611167',3,2,N'0',N'0' union all
select '5285','2008-4-28 8:41',49,N'00003611167',3,2,N'0',N'0' union all
select '5349','2008-4-28 15:54',49,N'00003611167',3,2,N'0',N'0' union all
select '5430','2008-4-29 7:51',49,N'00003611167',3,2,N'0',N'0' union all
select '5497','2008-4-29 18:14',49,N'00003611167',3,2,N'0',N'0' union all
select '5563','2008-4-30 8:15',49,N'00003611167',3,2,N'0',N'0' 
select [EmployeeID],[CardNo],sum(datediff(hour,BeginTime,EndTime)) as WorkHour
from 
(
select [EmployeeID],[CardNo] , max(case when px%2=1 then [EventTime] end)  as BeginTime,max(case when px%2=0 then [EventTime] end) as EndTime
from (
select px = (select count(1) from @tb where [EmployeeID] = t.[EmployeeID] and [CardNo] = t.[CardNo] and [EventTime] <= t.[EventTime] ),
  [EventTime],[EmployeeID],[CardNo]
from @tb t ) A
group by [EmployeeID],[CardNo],(px+1)/2
) B
group by [EmployeeID],[CardNo]
/*
EmployeeID  CardNo      WorkHour
----------- ----------- -----------
49          00003611167 78
*/

Answer 1

--精确到分钟,除以60转换为小时.
select [EmployeeID],[CardNo],sum(datediff(n,BeginTime,EndTime))*1.0/60 as WorkHour -- n 表示分钟
from 
(
select [EmployeeID],[CardNo] , max(case when px%2=1 then [EventTime] end)  as BeginTime,max(case when px%2=0 then [EventTime] end) as EndTime
from (
select px = (select count(1) from @tb where [EmployeeID] = t.[EmployeeID] and [CardNo] = t.[CardNo] and [EventTime] <= t.[EventTime] ),
  [EventTime],[EmployeeID],[CardNo]
from @tb t ) A
group by [EmployeeID],[CardNo],(px+1)/2
) B
group by [EmployeeID],[CardNo]
/*
EmployeeID  CardNo      WorkHour
----------- ----------- ----------
49          00003611167 74.900000
*/

Answer 2

我补上2005的写法:
declare @t table(EventID int,EventTime Datetime,EmployeeID int,CardNo nvarchar(11),DoorID int,ControlID int,EventType nvarchar(1),Modify nvarchar(1))
Insert @t
select '3825','2008-4-18 7:54',49,N'00003611167',3,2,N'0',N'0' union all
select '3919','2008-4-18 16:15',49,N'00003611167',3,2,N'0',N'0' union all
select '4018','2008-4-19 8:28',49,N'00003611167',3,2,N'0',N'0' union all
select '4042','2008-4-19 15:04',49,N'00003611167',3,2,N'0',N'0' union all
select '4257','2008-4-21 7:57',49,N'00003611167',3,2,N'0',N'0' union all
select '4322','2008-4-21 15:12',49,N'00003611167',3,2,N'0',N'0' union all
select '4441','2008-4-22 8:03',49,N'00003611167',3,2,N'0',N'0' union all
select '4546','2008-4-22 17:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4588','2008-4-23 8:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4618','2008-4-23 16:07',49,N'00003611167',3,2,N'0',N'0' union all
select '4677','2008-4-24 8:36',49,N'00003611167',3,2,N'0',N'0' union all
select '4725','2008-4-24 19:04',49,N'00003611167',3,2,N'0',N'0' union all
select '4864','2008-4-25 8:29',49,N'00003611167',3,2,N'0',N'0' union all
select '4948','2008-4-25 16:03',49,N'00003611167',3,2,N'0',N'0' union all
select '5285','2008-4-28 8:41',49,N'00003611167',3,2,N'0',N'0' union all
select '5349','2008-4-28 15:54',49,N'00003611167',3,2,N'0',N'0' union all
select '5430','2008-4-29 7:51',49,N'00003611167',3,2,N'0',N'0' union all
select '5497','2008-4-29 18:14',49,N'00003611167',3,2,N'0',N'0' union all
select '5563','2008-4-30 8:15',49,N'00003611167',3,2,N'0',N'0' 
;with s as 
(select *,row_number() over(partition by employeeid order by eventtime) as rn from @t)
select a.employeeid,sum(datediff(n,a.eventtime,b.eventtime))/60.0 as wh
from s a
inner join s b on a.employeeid=b.employeeid and a.rn+1=b.rn
where a.rn%2=1 
group by a.employeeid