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

2008-07-13 08:37:48 183 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

黑盒测试设计专题：边界值点击上方 BeTester ，学习更多知识让碎片成体系，让测试更专业当我们还是学生时，最彷徨的莫过于得知自己的考试成绩仅差 “半分” 就达到目标。即便只有“半分”差异，回到家却是一种天上与炼狱的待遇。这种处于临界的差异会产生截然不同的状态，使得结果发生天翻地覆的变化。软件测试也不例外，很多代码对边界处理不当，严重时导致系统崩溃。这种通过对临界条件的测试分析方法叫做边界值测试设计。 1、... 阅读详情

实验室想利用门禁系统实现考勤功能，门禁刷卡的记录是这样的
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

打算这么计算，也符合实验室工作实际，个人每天第一次刷卡为进入，第二次为出去，第三次刷卡又判定为进入，第四次刷卡又为出去........依次类推，且不限定刷卡次数，以第一次进入和第一次出去为时间计算工作时间，第二次进入和第二次出去为工作时间....并将时间累加计算。（老板不出钱，就有一个读卡器读头）每周我还要根据此做出出勤及工作时长的报表，很是麻烦。谢谢诸位高手

...全文

生产环境中的机器学习：从模型上线到系统契约机器学习模型部署不是代码发布的终点，而是作为业务系统组件的工程化起点。其核心挑战在于模型与真实数据流、依赖服务、业务规则之间的动态适配——这涉及输入/输出/失败/演进四类契约设计、批流一致性保障、幂等性与降级可观测性等关键工程实践。在金融、风控等强监管场景中，延迟监控需聚焦P99.9而非P95，漂移检测须区分概念/数据/协变量三类变化并匹配响应节奏，而模型治理的本质是构建可审计、可追溯、可打印的决策健康档案（DHR）。这些能力共同支撑起模型在真实世界中的鲁棒性、可信性与可持续性。阅读详情

koust

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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?

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

相关推荐