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

koust 2008-07-13 08:37:48
实验室想利用门禁系统实现考勤功能，门禁刷卡的记录是这样的
EventID EventTime EmployeeID CardNo DoorID ControlID EventType Modify
3825 2008-4-18 7:54 49 00003611167 3 2 0 0
3919 2008-4-18 16:15 49 00003611167 3 2 0 0
4018 2008-4-19 8:28 49 00003611167 3 2 0 0
4042 2008-4-19 15:04 49 00003611167 3 2 0 0
4257 2008-4-21 7:57 49 00003611167 3 2 0 0
4322 2008-4-21 15:12 49 00003611167 3 2 0 0
4441 2008-4-22 8:03 49 00003611167 3 2 0 0
4546 2008-4-22 17:36 49 00003611167 3 2 0 0
4588 2008-4-23 8:36 49 00003611167 3 2 0 0
4618 2008-4-23 16:07 49 00003611167 3 2 0 0
4677 2008-4-24 8:36 49 00003611167 3 2 0 0
4725 2008-4-24 19:04 49 00003611167 3 2 0 0
4864 2008-4-25 8:29 49 00003611167 3 2 0 0
4948 2008-4-25 16:03 49 00003611167 3 2 0 0
5285 2008-4-28 8:41 49 00003611167 3 2 0 0
5349 2008-4-28 15:54 49 00003611167 3 2 0 0
5430 2008-4-29 7:51 49 00003611167 3 2 0 0
5497 2008-4-29 18:14 49 00003611167 3 2 0 0
5563 2008-4-30 8:15 49 00003611167 3 2 0 0

打算这么计算，也符合实验室工作实际，个人每天第一次刷卡为进入，第二次为出去，第三次刷卡又判定为进入，第四次刷卡又为出去........依次类推，且不限定刷卡次数，以第一次进入和第一次出去为时间计算工作时间，第二次进入和第二次出去为工作时间....并将时间累加计算。（老板不出钱，就有一个读卡器读头）每周我还要根据此做出出勤及工作时长的报表，很是麻烦。谢谢诸位高手
...全文
111 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
wgzaaa 2008-07-13
  • 打赏
  • 举报
 回复
有人怀疑没打上，再刷一下就出去了？会有抱怨的，可能还会其他问题
Andy-W 2008-07-13
  • 打赏
  • 举报
 回复
根据：

“算这么计算，也符合实验室工作实际，个人每天第一次刷卡为进入，第二次为出去，第三次刷卡又判定为进入，第四次刷卡又为出去.”

突然下班时间，考勤机刚好坏了，那么他第二天上班时间就变成了上一天的下班时间了。

sdxiong 2008-07-13
  • 打赏
  • 举报
 回复
我补上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
hery2002 2008-07-13
  • 打赏
  • 举报
 回复 
--精确到分钟,除以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

*/
hery2002 2008-07-13
  • 打赏
  • 举报
 回复
精确到小时还是分钟?
hery2002 2008-07-13
  • 打赏
  • 举报
 回复 
-->生成测试数据

 

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

*/
hery2002 2008-07-13
  • 打赏
  • 举报
 回复
sql 2000?
sql 2005?
软件测试经典面试题剖析
课程特点：贴合实际：囊括13个最常见的面试问题，覆盖技术面及人事面，直击面试痛点趣味实用：以面试官、应聘者的角度一问一答的形式开展，示例满分回答，并辅以题型总结高效精讲：每个知识点覆盖企业主流的面试场景...
我在CSDN参与的3000个帖子
2：100分急求，随机输出十个小写字母，但是，要求这十个字母不相同 3：求Sn=a+aa+aaa+…+aaa…a（n个a）之值 4：数组题 望高手帮忙！ 5：呵呵，来推荐一下我的网站，本站提供大量当今流行的免费的音乐和免费电影，...
软件设计是怎样炼成的（8）——用户感觉好才是真的好（用户体验设计）
负责这个岗位的同事至少需要具备这样的一个素养：就是能从用户的角度来思考问题！如果能同时具备技术功底，能设计出用户体验极佳而且开发工作量少的界面整体规划，这样就最完美了！     9.3 统一界面标准 ...
【小红书】春招&；实习软开岗位超丰富，笔试还有一轮，快投呀！！
之前一直沉浸在考研失败的痛苦里，那么多时间，精力，更多的是迷茫不确定未来要干嘛，然后突然某天想通了，鼠鼠两份大厂实习，但是学历双非非科班排序全部挂掉，只有这两个机会，求大佬给给建议，目前准备签第一个，
南昌大学软件学院20级四年课程
寒冷的冬天已销声匿迹，迎来了暖暖的春季，今天是我们 新年开学的第一天， 我们刚刚度过了一个祥和的春节，度过了一个 愉快的寒假。现在我们回到了熟悉的校园，迎来了“牛”气冲天的牛年！ 本学期我们将学习大学物理...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章