--员工表
create table #employee
(id int identity(1,1),gh varchar(10),dept_no varchar(10),name varchar(10))insert into #employee
select '10001','001','张无忌' union all
select '10002','001','张三丰' union all
select '10003','002','韦小宝' union all
select '10004','002','成近南' union all
select '10005','003','和申'--部门表
create table #dept
(id int identity(1,1),dept_no varchar(10),dept_name varchar(10))insert into #dept
select '001','IT部' union all
select '002','产品部' union all
select '003','制造部'--考勤表create table #check_work
(id int identity(1,1),person_number varchar(10),type varchar(10),
begindate datetime, --开始考勤时间
enddate datetime --结束考勤时间
)
-- case when type=员工 then person_number=员工编号
-- when type=部门 then person_number=部门编号
insert into #check_work
select '001','部门','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1003','员工','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1004','员工','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1005','员工','2008-05-05 00:00:00','2008-05-09 00:00:00'--班次表
create table #workset(id int identity(1,1),
workset_number varchar(10), --班次编号
workset_name varchar(20), --班次名称
begindate varchar(5),enddate varchar(5), --上班时间,下班时间
begindate1 varchar(5),enddate1 varchar(5) , --上班打卡起始时间和上班打卡结束时间
begindate2 varchar(5),enddate2 varchar(5), --下班打卡起始时间和下班打卡结束时间
rest_begin1 varchar(5),rest_end1 varchar(5) --中间休息起始时间和结束时间 ,在这里暂不考虑
)insert into #workset
select '01','管理干部','09:00','18:00','06:00','09:00','18:00','23:59','12:00','13:00' union all --干部班次一天只打两次卡
select '02','员工正常班次一','08:00','11:30','07:00','08:00','11:30','12:00','','' union all --这个班次没有休息时间
select '03','员工正常班次二','13:00','17:30','12:00','13:00','17:30','17:59','','' union all --这个班次没有休息时间
select '04','冲压车间夜班班次','20:00','04:30','19:30','20:00','04:30','05:00','23:00','00:00' --打卡记录表
create table #ropen
(id int identity(1,1),
gh varchar(10), --员工工号
open_time datetime --打卡时间
)insert into #ropen
select '10001','2008-05-05 08:54:00.000' union all --从这开始部门为IT部,属干部班次 09:00-18:00
select '10001','2008-05-05 08:58:00.000' union all
select '10001','2008-05-05 18:04:00.000' union all
select '10002','' union all --10002 上午没打上卡
select '10002','2008-05-05 18:15:00.000' union all
select '10002','2008-05-05 18:20:00.000' union allselect '10003','2008-05-05 07:54:00.000' union all --从这开始是员工班次, 08:00-11:30,13:00=17:30
select '10003','2008-05-05 11:40:00.000' union all
select '10003','2008-05-05 12:54:00.000' union all --10003 第二个正常班次打卡记录
select '10003','2008-05-05 17:30:00.000' union all
select '10003','2008-05-05 17:54:00.000' union all
select '10004','2008-05-05 07:58:00.000' union all
select '10004','' union all --10004第一个班次下班没有打上卡
select '10004','2008-05-05 12:54:00.000' union all
select '10004','2008-05-05 17:54:00.000' union allselect '10005','2008-05-05 19:58:00.000' union all --从这开是夜班 20:00-04:30
select '10005','2008-05-06 04:51:00.000' --drop table #employee,#dept,#check_work,#workset,#ropen根据#check_work表中排了考勤的员工的打卡记录自动
得到 10001,10002是属于'01'班次
10003,10004是属于 '02','03'班次
10005 是属于'04'班次
第一次弄考勤,任务崔得太急了。c友们帮我看看,提提建义也好。
来者都是客,人人有分。
create table #employee
(id int identity(1,1),gh varchar(10),dept_no varchar(10),name varchar(10))insert into #employee
select '10001','001','张无忌' union all
select '10002','001','张三丰' union all
select '10003','002','韦小宝' union all
select '10004','002','成近南' union all
select '10005','003','和申'--部门表
create table #dept
(id int identity(1,1),dept_no varchar(10),dept_name varchar(10))insert into #dept
select '001','IT部' union all
select '002','产品部' union all
select '003','制造部'--考勤表create table #check_work
(id int identity(1,1),person_number varchar(10),type varchar(10),
begindate datetime, --开始考勤时间
enddate datetime --结束考勤时间
)
-- case when type=员工 then person_number=员工编号
-- when type=部门 then person_number=部门编号
insert into #check_work
select '001','部门','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1003','员工','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1004','员工','2008-05-05 00:00:00','2008-05-09 00:00:00' union all
select '1005','员工','2008-05-05 00:00:00','2008-05-09 00:00:00'--班次表
create table #workset(id int identity(1,1),
workset_number varchar(10), --班次编号
workset_name varchar(20), --班次名称
begindate varchar(5),enddate varchar(5), --上班时间,下班时间
begindate1 varchar(5),enddate1 varchar(5) , --上班打卡起始时间和上班打卡结束时间
begindate2 varchar(5),enddate2 varchar(5), --下班打卡起始时间和下班打卡结束时间
rest_begin1 varchar(5),rest_end1 varchar(5) --中间休息起始时间和结束时间 ,在这里暂不考虑
)insert into #workset
select '01','管理干部','09:00','18:00','06:00','09:00','18:00','23:59','12:00','13:00' union all --干部班次一天只打两次卡
select '02','员工正常班次一','08:00','11:30','07:00','08:00','11:30','12:00','','' union all --这个班次没有休息时间
select '03','员工正常班次二','13:00','17:30','12:00','13:00','17:30','17:59','','' union all --这个班次没有休息时间
select '04','冲压车间夜班班次','20:00','04:30','19:30','20:00','04:30','05:00','23:00','00:00' --打卡记录表
create table #ropen
(id int identity(1,1),
gh varchar(10), --员工工号
open_time datetime --打卡时间
)insert into #ropen
select '10001','2008-05-05 08:54:00.000' union all --从这开始部门为IT部,属干部班次 09:00-18:00
select '10001','2008-05-05 08:58:00.000' union all
select '10001','2008-05-05 18:04:00.000' union all
select '10002','' union all --10002 上午没打上卡
select '10002','2008-05-05 18:15:00.000' union all
select '10002','2008-05-05 18:20:00.000' union allselect '10003','2008-05-05 07:54:00.000' union all --从这开始是员工班次, 08:00-11:30,13:00=17:30
select '10003','2008-05-05 11:40:00.000' union all
select '10003','2008-05-05 12:54:00.000' union all --10003 第二个正常班次打卡记录
select '10003','2008-05-05 17:30:00.000' union all
select '10003','2008-05-05 17:54:00.000' union all
select '10004','2008-05-05 07:58:00.000' union all
select '10004','' union all --10004第一个班次下班没有打上卡
select '10004','2008-05-05 12:54:00.000' union all
select '10004','2008-05-05 17:54:00.000' union allselect '10005','2008-05-05 19:58:00.000' union all --从这开是夜班 20:00-04:30
select '10005','2008-05-06 04:51:00.000' --drop table #employee,#dept,#check_work,#workset,#ropen根据#check_work表中排了考勤的员工的打卡记录自动
得到 10001,10002是属于'01'班次
10003,10004是属于 '02','03'班次
10005 是属于'04'班次
第一次弄考勤,任务崔得太急了。c友们帮我看看,提提建义也好。
来者都是客,人人有分。
---------------------我觉得这个问题就不要交给电脑去识别了吧。有打卡记录可以尝试各种努力识别出来,尚且不能保证100%准确,没有打卡记录,那就真晕了,参考前后几天的打卡记录?所以这里应该建排班表。
小楼兄,现在好多考勤软件都是自动识别班次的.
现在员工白班就有两个班次,要是赶货的话,会遇上两班倒,三班倒,班次就多了.
我就算在#check_work考勤设定表中加一列(workset_number)班次编号 中指定它有可能值为('02','03','04')这么几个
班次,也要根据当天的打卡记录找出具体是哪个班次。
我们目前这样做的目的是,一下子指定某个部门可能存在哪几个班次,以后呢人事部就不要输入很多内容,一下子就可以指定这个部门
一个月或一年的考勤情况.
select distinct gh,workset_number,workset_name from #ropen a,#workset b
where ((convert(char(8),a.open_time,108) between b.begindate1 and b.enddate1) or a.open_time = '')
and exists(select 1 from #ropen where a.gh=gh and a.open_time <> open_time and
((convert(char(8),open_time,108) between b.begindate2 and b.enddate2) or open_time = ''))
/*
gh workset_number workset_name
---------- -------------- --------------------
10001 01 管理干部
10002 01 管理干部
10003 02 员工正常班次一
10003 03 员工正常班次二
10004 01 管理干部
10004 02 员工正常班次一
10004 03 员工正常班次二
10005 04 冲压车间夜班班次
*/
还是应该对员工先进行排班,再算考勤,需要的表为: 一个班次表(有一个班次为默认班次), 一个临时班次表,一个轮班表, 写一个函数来查找某个人某一天到底上什么班次! 查找方式为1:先查临时班次表, 看这个人在某一天是否排了临时班,如果有则return这个临时班次, 2: 查轮班表,轮班表的数据为,例如某个人从2008-05-01号开始上A这个班次, 那么今天是2008-05-20日, 那么返回的就是A这个班次, 如果轮班也没有,那么就返回的就是班次表里面的默认班次。 得到按此以后,再根据班次的时间段, 到打卡表里面去抓相应的时间。
呵呵,我不否认可以,但不能100%准确。
很简单的道理,给一堆数据,如果存在两种/多种的可能,有时候人脑都无法判别的属于哪个班,具体要看这个系统班别的复杂度了。来外一个建议:workset表的时间不要用varchar,应该用datetime,让他等于类似'1900-01-01 08:00'这种形式。
写一个函数,参数为开始考勤日期比如:2008-05-01,和考勤天数,比如31日,用这个日期范围和workset表产生一个笛卡积。
用这个函数和考勤表关联查询,就不需要对考勤表的时间进行转换,这样会方便很多。
很简单的道理,给一堆数据,如果存在两种/多种的可能,有时候人脑都无法判别的属于哪个班,具体要看这个系统班别的复杂度了。 来外一个建议:workset表的时间不要用varchar,应该用datetime,让他等于类似'1900-01-01 08:00'这种形式。
写一个函数,参数为开始考勤日期比如:2008-05-01,和考勤天数,比如31日,用这个日期范围和workset表产生一个笛卡积。
用这个函数和考勤表关联查询,就不需要对考勤表的时间进行转换,这样会方便很多。---------------------
谢谢小楼提醒,实际数据库中是datetime类型,只是我设计测试数据时搞的varchar类型,以后我会注意的.
select gh,b=begindate,e=enddate into #c from #employee a,#check_work b where (case when type = '员工' then a.gh else a.dept_no end) = b.person_number --获得检查范围内的打卡数据
select gh,d=convert(char(10),open_time,120),t= convert(char(8),open_time,108) into #d from #ropen a
where exists(select 1 from #c where a.gh=gh and a.open_time between b and e)--检查符合上班和下班时间的班次,两个班次的上下班时间不能交叉重叠
select distinct gh,d,wno=workset_number,wname=workset_name into #w from #d a,#workset b
where a.t between begindate1 and enddate1
and exists(select 1 from #d where a.gh = gh and t between b.begindate2 and b.enddate2
and cast(d as datetime)=cast(a.d as datetime)+case when b.begindate2<b.begindate1 then 1 else 0 end)--只打一次卡,如果该时间段只有一个班次就记录,否则放弃
insert #w
select distinct gh,d,wno=workset_number,wname=workset_name from #d a,#workset b
where (a.t between begindate1 and enddate1 or a.t between begindate2 and enddate2)
and not exists(select 1 from #workset where b.id <> id
and (a.t between begindate1 and enddate1 or a.t between begindate2 and enddate2))
and not exists(select 1 from #w where a.gh = gh and a.d = d and b.workset_number = wno)--上下班都不打卡没办法判断select * from #w order by gh
/*
gh d wno wname
---------- ---------- ---------- --------------------
10001 2008-05-05 01 管理干部
10002 2008-05-05 01 管理干部
10003 2008-05-05 02 员工正常班次一
10003 2008-05-05 03 员工正常班次二
10004 2008-05-05 03 员工正常班次二
10005 2008-05-05 04 冲压车间夜班班次
10005 2008-05-06 04 冲压车间夜班班次
*/
如果班次时间表变化呢,直接造成结果都不准了。还是把逻辑写在程序里比较妥当
select gh,b=begindate,e=enddate into #c from #employee a,#check_work b where (case when type = '员工' then a.gh else a.dept_no end) = b.person_number --获得检查范围内的打卡数据#d
select gh,d=convert(char(10),open_time,120),t= convert(char(8),open_time,108) into #d from #ropen a
where exists(select 1 from #c where a.gh=gh and a.open_time between b and e)
--清除无效打卡数据#d
delete a from #d a where not exists(select 1 from #workset where t between begindate1 and enddate1 or t between begindate2 and enddate2)--检查符合上班和下班时间的班次,两个班次的上下班时间不能交叉重叠#w
select distinct gh,d,wno=workset_number,wname=workset_name into #w from #d a,#workset b
where a.t between begindate1 and enddate1
and exists(select 1 from #d where a.gh = gh and t between b.begindate2 and b.enddate2
and cast(d as datetime)=cast(a.d as datetime)+case when b.begindate2<b.begindate1 then 1 else 0 end)
--获取未匹配的记录#dd
select * into #dd from #d
delete a from #dd a,#workset b
where (a.t between begindate1 and enddate1
and exists(select 1 from #d where a.gh = gh and t between b.begindate2 and b.enddate2
and cast(d as datetime)=cast(a.d as datetime)+case when b.begindate2<b.begindate1 then 1 else 0 end))
or (a.t between begindate2 and enddate2
and exists(select 1 from #d where a.gh = gh and t between b.begindate1 and b.enddate1
and cast(d as datetime)=cast(a.d as datetime)-case when b.begindate2<b.begindate1 then 1 else 0 end))--只打一次卡,如果该时间段只有一个班次就记录,否则放弃
insert #w
select distinct gh,d,wno=workset_number,wname=workset_name from #dd a,#workset b
where (a.t between begindate1 and enddate1 or a.t between begindate2 and enddate2)
and not exists(select 1 from #w where a.gh = gh and a.d = d and b.workset_number = wno)
and not exists(select 1 from #workset c where c.id <> b.id
and (a.t between c.begindate1 and c.enddate1 or a.t between begindate2 and enddate2)
and not exists(select 1 from #d where d = a.d and (t not between b.begindate1 and b.enddate1)
and t between c.begindate and c.enddate))--上下班都不打卡没办法判断select * from #w order by gh,wno
/*
gh d wno wname
---------- ---------- ---------- --------------------
10001 2008-05-05 01 管理干部
10002 2008-05-05 01 管理干部
10003 2008-05-05 02 员工正常班次一
10003 2008-05-05 03 员工正常班次二
10004 2008-05-05 02 员工正常班次一
10004 2008-05-05 03 员工正常班次二
10005 2008-05-05 04 冲压车间夜班班次
*/
2.放弃:如果存在只打卡一次,并且该打卡时间对应2个或以上班次的打卡时间(上下班),而且不能通过当天的其它打卡时间判定出唯一班次,则无法判断,将放弃该打卡。
3.错判:如果存在三个班次,班次1 的上班打卡时间与班次2 的上班打卡时间重叠,班次1的下班打卡时间与班次3的下班打卡时间重叠,正巧某员工是班次2和班次3,但班次2上班打卡,下班没打卡,班次3上班没打卡但下班打卡,则系统将误判未班次1。--获得需要检查的数据#c
select gh,b=begindate,e=enddate into #c from #employee a,#check_work b where (case when type = '员工' then a.gh else a.dept_no end) = b.person_number --获得检查范围内的打卡数据#d
select gh,d=convert(char(10),open_time,120),t= convert(char(8),open_time,108) into #d from #ropen a
where exists(select 1 from #c where a.gh=gh and a.open_time between b and e)
--清除无效打卡数据#d
delete a from #d a where not exists(select 1 from #workset where t between begindate1 and enddate1 or t between begindate2 and enddate2)--检查符合上班和下班时间的班次,两个班次的上下班时间不能交叉重叠#w
select distinct gh,d,wno=workset_number,wname=workset_name into #w from #d a,#workset b
where a.t between begindate1 and enddate1
and exists(select 1 from #d where a.gh = gh and t between b.begindate2 and b.enddate2
and cast(d as datetime)=cast(a.d as datetime)+case when b.begindate2<b.begindate1 then 1 else 0 end)
--获取未匹配的记录#dd
select * into #dd from #d
delete a from #dd a,#workset b
where (a.t between begindate1 and enddate1
and exists(select 1 from #d where a.gh = gh and t between b.begindate2 and b.enddate2
and cast(d as datetime)=cast(a.d as datetime)+case when b.begindate2<b.begindate1 then 1 else 0 end))
or (a.t between begindate2 and enddate2
and exists(select 1 from #d where a.gh = gh and t between b.begindate1 and b.enddate1
and cast(d as datetime)=cast(a.d as datetime)-case when b.begindate2<b.begindate1 then 1 else 0 end))--只打一次卡,如果该时间段只有一个班次就记录,否则放弃
insert #w
select distinct gh,d,wno=workset_number,wname=workset_name from #dd a,#workset b
where (a.t between begindate1 and enddate1 or a.t between begindate2 and enddate2)
and not exists(select 1 from #w where a.gh = gh and a.d = d and b.workset_number = wno)
and not exists(select 1 from #workset c where c.id <> b.id
and (a.t between c.begindate1 and c.enddate1 or a.t between begindate2 and enddate2)
and not exists(select 1 from #d where d = a.d and (t not between b.begindate1 and b.enddate1)
and ((c.begindate1>=c.enddate2 and t between c.begindate1 and c.enddate2)
or(c.begindate1<c.enddate2 and (t >=c.enddate2 or t <=c.begindate1)))
))--修正跨天班次的判定。--上下班都不打卡没办法判断select * from #w order by gh,wno
/*
gh d wno wname
---------- ---------- ---------- --------------------
10001 2008-05-05 01 管理干部
10002 2008-05-05 01 管理干部
10003 2008-05-05 02 员工正常班次一
10003 2008-05-05 03 员工正常班次二
10004 2008-05-05 02 员工正常班次一
10004 2008-05-05 03 员工正常班次二
10005 2008-05-05 04 冲压车间夜班班次
*/
create table #workset(id int identity(1,1),
workset_number varchar(10), --班次编号
workset_name varchar(20), --班次名称
begindate varchar(5),enddate varchar(5), --上班时间,下班时间
begindate1 varchar(5),enddate1 varchar(5) , --上班打卡起始时间和上班打卡结束时间
begindate2 varchar(5),enddate2 varchar(5), --下班打卡起始时间和下班打卡结束时间
rest_begin1 varchar(5),rest_end1 varchar(5) --中间休息起始时间和结束时间 ,在这里暂不考虑
)insert into #workset
select '01','管理干部','09:00','18:00','06:00','09:30','17:30','23:59','12:00','13:00' union all --干部班次一天只打两次卡
select '02','员工正常班次一','08:00','11:30','07:00','08:30','11:00','12:00','','' union all --这个班次没有休息时间
select '03','员工正常班次二','13:00','17:30','12:01','13:30','17:00','17:59','','' union all --这个班次没有休息时间
select '04','冲压车间夜班班次','20:00','04:30','19:30','20:30','04:00','05:00','23:00','00:00' --打卡记录表
create table #ropen
(id int identity(1,1),
gh varchar(10), --员工工号
open_time datetime --打卡时间
)insert into #ropen
select '10001','2008-05-05 08:54:00.000' union all --从这开始部门为IT部,属干部班次 09:00-18:00
select '10001','2008-05-05 08:58:00.000' union all
select '10001','2008-05-05 18:04:00.000' union all
select '10002','' union all --10002 上午没打上卡
select '10002','2008-05-05 18:15:00.000' union all
select '10002','2008-05-05 18:20:00.000' union allselect '10003','2008-05-05 07:54:00.000' union all --从这开始是员工班次, 08:00-11:30,13:00=17:30
select '10003','2008-05-05 11:40:00.000' union all
select '10003','2008-05-05 12:54:00.000' union all --10003 第二个正常班次打卡记录
select '10003','2008-05-05 17:30:00.000' union all
select '10003','2008-05-05 17:54:00.000' union all
select '10004','2008-05-05 07:58:00.000' union all
select '10004','' union all --10004第一个班次下班没有打上卡
select '10004','2008-05-05 12:54:00.000' union all
select '10004','2008-05-05 17:54:00.000' union allselect '10005','2008-05-05 19:58:00.000' union all --从这开是夜班 20:00-04:30
select '10005','2008-05-06 04:50:00.000' union all
select '10005','2008-05-06 04:51:00.000'
这两个表的数据变化了下,就问题来了.判断不准确了.你现在回复的代码,我来测测看。
我刚测了下你现在发的代码,如用上面我发的两个表数据来测的话,有点小问题gh d wno wname
---------- ---------- ---------- --------------------
10002 2008-05-05 01 管理干部
10001 2008-05-05 01 管理干部
10003 2008-05-05 01 管理干部 --这条有问题
10003 2008-05-05 02 员工正常班次一
10003 2008-05-05 03 员工正常班次二
10004 2008-05-05 01 管理干部 --这条有问题
10004 2008-05-05 03 员工正常班次二
10005 2008-05-05 04 冲压车间夜班班次(所影响的行数为 8 行)
1):
A班次: 8:00-12:00
B班次:8:30-12:30
某個人 8:10打卡, 電腦該算他A班遲到, 還是B班早到?
2):
1: 假設某個人在某天下午應該上班時間為14:00 -> 18:00, 他上班打卡為14:20(遲到20分),
2: 然后他又請假一個小時(16:00->17:00),請假同樣需要打卡,他的打卡為 離:15:53, 來:17:30 (遲到30分)
3:下班打卡為17:55(早退5分)如何處理? :)看你的班次里面還有夜班跨天的,估計班次也不是每天8小時1個班次那么簡單。
你還是和你的老大溝通一下,多給你點時間,我當時做考勤的時候, 就光考勤計算的sql就寫了1000多行。
这位朋友说到我心坎去了,当初我们经理说要这样做的时候,我就反对过实现起来真是困难。因为这期间的异常太多了,情况种类
也太多了,工厂里当然有夜班了,既然是夜班就跨天了。我们有一个班次是厨房的,每天工作9.5小时。根本不是统一一个班次8小时的
上面你也看到了,我们有的是打两次卡,这样就中间有休息时间(目前我设置了4个休息时间段),请假都会算死人,我用了十几个 case when
才搞定。就统计个请假时间,就用了几百行代码。现在网上那些说可以实现自动识别班次的,也不知道他们是怎么做的,我也没有接触过。
可以问下,你以前的考勤是每周排吗?
如:
部门/员工 开始时间 结束时间 班次 006 2008-05-05 2008-05-10 02
006 2008-05-05 2008-05-10 03
006 2008-05-12 2008-05-17 04 --假设为夜班 目前我们是这样每周排的.现在我们老大的意思是部门/员工 开始时间 结束时间 班次 006 2008-05-05 2008-05-17 02,03,04
真是麻烦.
所以新來一個員工的話, 他如果沒有其他的班次, 根本不用排班, 電腦自動抓取他的8小時默認班次。 如果他要上一些特別的班次,
可有如下數據
A員工, 2008-01-01起, 上班次1
A員工, 2008-03-15起, 上班次2
那么電腦知道 A員工2008-03-10號上班次1, 2008-05-20號上班次2, 就是說只有換班的時候才排班,其他人事不用動。
而且又提供按部門,多員工同時排班, 排班還是很快的。
处理考勤打卡记录问题
http://www.cnblogs.com/wghao/archive/2007/06/13/782315.html