导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于考勤的逻辑算法问题

jycnet 2007-12-14 03:02:34
关于考勤的逻辑算法问题请教各位朋友:

1.公司是弹性工作制,即上班时间:早8:00~早:9:00 (早于8:00来算8:00)之间,中午休息时间为:12:00~13:00
下班时间为17:00~18:00之间。正常工作时间为8个小时 jr_time 为最早进门时间,wc_time为最晚出门时间。
2. 有三个特殊时间:
请假时间: mh_leave, 即员工可以在该天请假,请假以一个小时为单位(按填报),最多请8小时
加班时间:mh_overtime 即员工在该天弹性下班时间到后可以加班,加班以一个小时为单位(按填报),最多7小时 (即夜12点 后为第二天的加班)
3.特殊时间:fixtime ,即因为各种原因对该天增加或减少的工作时间,以一个小时为单位(即日工作时间增加或减少几个小时)

4.考勤制度:
a.在9:00 ~9:15 之间上班的为迟到,可以不补休假(但有迟到标记),晚于9:15 来的需要补假(以一个小时为单位,同时要计算补假时间),(例如:如果我早上请一个小时假,必须在10:00钟以前到,如果早上请三个小时假,可以13:00以前来但是必须在18点以后走,依此类推)
b.在弹性下班之前的15分钟内离开的为早退, 可以不补休假(但有早退标记),如果多于15分钟需要补假(以一个小时为单位,同时要计算补假时间)。
c.缺勤为:无出门和无进门记录。
d.无进为:只有出门记录。
e.无出为:只有进门记录。

5.
jr_fix 为主管锁定进门时间(可以忽略该记录)
wc_fix 为主管锁定出门时间(可以忽略该记录)
qq_fix 为主管锁定缺勤时间(可以忽略该记录)


我写了一个逻辑,大家给看看有没有遗漏或错误的地方,或者有什么好的建议和方法。

迟到
(case
when jr_time is not null and wc_time is not null and jr_fix = 0 and mh_leave = 0 and fixtime >= 0 and cast(jr_time as datetime) > '9:00:00' then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and mh_leave = 0 and fixtime < 0 and abs(fixtime) < 3 and cast(jr_time as datetime) > dateadd(minute, abs(fixtime) * 60, '9:00:00') then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and mh_leave = 0 and fixtime < 0 and abs(fixtime) >= 3 and cast(jr_time as datetime) > dateadd(minute, abs(fixtime) * 60, '10:00:00') then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and mh_leave > 0 and mh_leave < 3 and fixtime >= 0 and cast(jr_time as datetime) > dateadd(minute, mh_leave * 60, '9:00:00') then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and mh_leave < 8 and mh_leave >= 3 and fixtime >= 0 and cast(jr_time as datetime) > dateadd(minute, mh_leave * 60, '10:00:00') then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and (mh_leave + abs(fixtime)) < 3 and fixtime < 0 and cast(jr_time as datetime) > dateadd(minute, (mh_leave + abs(fixtime)) * 60, '9:00:00') then 1
when jr_time is not null and wc_time is not null and jr_fix = 0 and (mh_leave + abs(fixtime)) >= 3 and fixtime < 0 and cast(jr_time as datetime) > dateadd(minute, (mh_leave + abs(fixtime)) * 60, '10:00:00') then 1
else 0 end) as user_cd



缺勤
(case
when jr_time is null and wc_time is null and qq_fix=0 and mh_leave<(8 + fixtime) then 1
when jr_time is null and wc_time is null and qq_fix=0 and mh_overtime > 0 then 1
else 0 end) as user_qq

无进
(case
when jr_time is null and wc_time is not null and jr_fix=0 and mh_leave<(8 + fixtime) then 1
when jr_time is null and wc_time is not null and jr_fix=0 and mh_overtime > 0 then 1
else 0 end) as user_wj

无出
(case
when jr_time is not null and wc_time is null and wc_fix=0 and mh_leave<(8 + fixtime) then 1
when jr_time is not null and wc_time is null and wc_fix=0 and mh_overtime > 0 then 1
else 0 end) as user_wc


早退的还没写,因为要考虑加班。
...全文
155 点赞 收藏 12
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
keneas 2007-12-15
考勤制度过于复杂拉
回复
w2jc 2007-12-15
看得晕...
很复杂的考勤制度啊,要小心,一个bug就是很多人来找你麻烦了,
我就帮你顶吧
回复
fcuandy 2007-12-14
非常友情的帮顶
回复
pt1314917 2007-12-14
ddd
回复
dawugui 2007-12-14
--我只写了两个,不一定对,反正就是把你需要的条件放case里面,存在就是1,不存在就是0,也可能是多个判断.
create table tb(name varchar(10),sdate varchar(10),jr_time varchar(10),wc_time varchar(10),mh_leave int,mh_overtime int,fixtime int,jr_fix int,wc_fix int,qq_fix int)
insert into tb values('aaa','2007-10-10','08:15:20','17:16:13',0,0,0,0,0,0)--(正常)
insert into tb values('aaa','2007-10-11','09:10:20','18:16:18',0,0,0,0,0,0)--(迟到)
insert into tb values('aaa','2007-10-12','08:10:20','17:09:17',0,0,0,0,0,0)--(早退)
insert into tb values('aaa','2007-10-13', null , null ,0,0,0,0,0,0)--(缺勤)
insert into tb values('aaa','2007-10-14','07:58:09', null ,0,0,0,0,0,0)--(无出)
insert into tb values('aaa','2007-10-15', null ,'17:09:02',0,0,0,0,0,0)--(无进)
insert into tb values('aaa','2007-10-10','09:15:20','17:16:13',1,0,0,0,0,0)--(正常)
insert into tb values('aaa','2007-10-11','10:10:20','18:16:18',1,0,0,0,0,0)--(迟到)
insert into tb values('aaa','2007-10-12','08:10:20','16:09:17',1,0,0,0,0,0)--(早退)
go

select name,sdate,jr_time,wc_time,mh_leave,fixtime,
cd_user = case when mh_leave <=0 and jr_time <= '09:00:00' then 0 else 1 end,
zt_user = case when jr_time is not null and wc_time is not null and mh_leave <=0 and datediff(hh , cast(sdate + ' ' + jr_time as datetime), cast(sdate + ' ' + wc_time as datetime)) >= 9 then 0 else 1 end
from tb

drop table tb

/*
name sdate jr_time wc_time mh_leave fixtime cd_user zt_user
---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
aaa 2007-10-10 08:15:20 17:16:13 0 0 0 0
aaa 2007-10-11 09:10:20 18:16:18 0 0 1 0
aaa 2007-10-12 08:10:20 17:09:17 0 0 0 0
aaa 2007-10-13 NULL NULL 0 0 1 1
aaa 2007-10-14 07:58:09 NULL 0 0 0 1
aaa 2007-10-15 NULL 17:09:02 0 0 1 1
aaa 2007-10-10 09:15:20 17:16:13 1 0 1 1
aaa 2007-10-11 10:10:20 18:16:18 1 0 1 1
aaa 2007-10-12 08:10:20 16:09:17 1 0 1 1

(所影响的行数为 9 行)
*/
回复
jycnet 2007-12-14
cd_user,zt_user,qq_user,wj_user,wc_user
你结果表的这几个字段是什么意思?

如果满足迟到的条件 cd_user =1 否则 为 0
如果满足早退的条件 zt_user =1 否则 为 0
如果满足缺勤的条件 qq_user =1 否则 为 0
如果满足无进的条件 wj_user =1 否则 为 0
如果满足无出的条件 wc_user =1 否则 为 0

这些主要是方便标识和以后统计人数用。
回复
victoryzll 2007-12-14
没时间搞
下次帮你
回复
dawugui 2007-12-14
cd_user,zt_user,qq_user,wj_user,wc_user
你结果表的这几个字段是什么意思?
回复
fwacky 2007-12-14
友情帮定
回复
jycnet 2007-12-14
你给出具体的结构,数据,算法和结果来.

比方一个表A
name,sdate,jr_time,wc_time,mh_leave,mh_overtime,fixtime,jr_fix,wc_fix,qq_fix
aaa,2007-10-10,8:15:20,17:16:13,0,0,0,0,0,0(正常)
aaa,2007-10-11,9:10:20,18:16:18,0,0,0,0,0,0(迟到)
aaa,2007-10-12,8:10:20,17:09:17,0,0,0,0,0,0(早退)
aaa,2007-10-13,null,null,0,0,0,0,0,0(缺勤)
aaa,2007-10-14,7:58:09,null,0,0,0,0,0,0(无出)
aaa,2007-10-15,null,17:09:02,0,0,0,0,0,0(无进)

aaa,2007-10-10,9:15:20,17:16:13,1,0,0,0,0,0(正常)
aaa,2007-10-11,10:10:20,18:16:18,1,0,0,0,0,0(迟到)
aaa,2007-10-12,8:10:20,16:09:17,1,0,0,0,0,0(早退)

想得到另一个表B
name,sdate,jr_time,wc_time,mh_leave,mh_overtime,fixtime,cd_user,zt_user,qq_user,wj_user,wc_user
aaa,2007-10-10,8:15:20,17:16:13,0,0,0,0,0,0,0,0
aaa,2007-10-11,9:10:20,18:16:18,0,0,0,1,0,0,0,0
..............................................依此类推

回复
kk19840210 2007-12-14
该回复帮顶
回复
dawugui 2007-12-14
又是case when判断的SQL.你给出具体的结构,数据,算法和结果来.

不然,看着头晕.
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告