关于跨日,多班次的考勤难题

[qianduo]

我想得到一个月员工的考勤报表
要求是根据班次表和打卡原始记录来显示员工在规定的上下班时间是否迟到，迟到多长时间，早退多长时间， 加班多长时间（单位最少是0.5小时），旷工多长时间
遇到星期六，跟日算加班。如果休息日上班算加班，不来不算旷工。

员工号 班次 打卡日期 时间1 时间2 迟到时间 早退时间 加班时间 旷工
A1459 MS2004.04.010844 1717 14 20 0
A1459 MS2004.04.020819 1910 0 0 1.5h
A1459 MS2004.04.030821 1740
.
.
A2176 LS2004.04.011932 0722 2 10 0
A2176 LS2004.04.021925 0832 0 0 1h
A2176 LS2004.04.03 1



还有上班打卡的有效时间段,下班打卡的有效时间段,防止重复多次打卡的现象发生.


shift_mst
班次 上班时间 下班时间 是否跨日上班 上班打卡的有效时间 上班打卡的有效时间
SHIFT_TYPE WORK_START WORK_END OVER_NIGHT START_PUNCH STOP_PUNCH OT_START OT_END OT_OVE_NIGHT
---------- ---------- -------- ----- -------
MS 08:30 17:30 N 06:30 10:30 14:00 19:00 N
LS 19:30 07:30 Y 17:30 21:30 05:30 09:30 N


休息日
班次 星期
SHIFT_TYPEDAY_OFF
MS 1
MS 7
LS 1

如MS 班次 正常上班时间为08：30 打卡的有效时间为 START_PUNCH 06：30--- STOP_PUNCH 10:30 如在这段时间打了2次，就取最大的一次为打卡上班的时间
如打卡的时间大于上班的时间就为迟到并得到迟到多少分钟。

下班正常下班时间为17：30 下班打卡的有效时间为 OT_START 14:00 --- OT_END 19：00 。如果在这短时间打多次就取最小的时间为下班打卡时间。
如果下班打卡时间-正常下班时间为加班时间。但加班时间要大于一个小时才算为加班时间。


打卡原始记录
record
员工号 班次 打卡日期 时间
emp_no shift_type date time
A1459 MS2004.04.010844
A1459 MS2004.04.011750
A1459 MS2004.04.020819
A1459 MS2004.04.021910
A1459 MS2004.04.030821
A1459 MS2004.04.031740
A1459 MS2004.04.040822
A1459 MS2004.04.041810
A2176 LS2004.04.011932
A2176 LS2004.04.020722
A2176 LS2004.04.021925
A2176 LS2004.04.030732
A2176 LS2004.04.041945
A2176 LS2004.04.050936

[qianduo]

大力，催健，马可，蚂蚁。。。大侠快来帮忙呀。

[qianduo]

请各位大侠提点意见，给出一点解决方案或思路~
~~急~~~

[qianduo]

测试数据:

create table record
(
emp_no varchar(10),
shift_type varchar(2),
T_date varchar(15),
T_time varchar(15)
)

insert record
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' union

select 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' union

select 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'

create table shift_mst
(
SHIFT_TYPE varchar(10),
WORK_START varchar(10),
WORK_END varchar(15),
OVER_NIGHT varchar(15),
START_PUNCH varchar(15),
STOP_PUNCH varchar(15),
OT_START varchar(15),
OT_END varchar(15),
OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','19:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','N'

[guoxinwei]

有难度，学习……

[qianduo]

create table record
(
emp_no varchar(10),
shift_type varchar(2),
T_date varchar(15),
T_time varchar(15)
)

insert record
select 'A1459','MS','2004.04.01','0744' union
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' union
select 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' union
select 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'

create table shift_sch_day
( shift_type varchar(10),
day_off int
)
insert shift_sch_day
select 'LS', 1 union
select 'LS', 7

create table emp_mst
(
emp_no varchar(10),
shift_type varchar(2)
)
insert emp_mst
select 'A1459', 'MS' union
select 'A2176', 'LS'


create table shift_mst
(
SHIFT_TYPE varchar(10),
WORK_START varchar(10),
WORK_END varchar(15),
OVER_NIGHT varchar(15),
START_PUNCH varchar(15),
STOP_PUNCH varchar(15),
OT_START varchar(15),
OT_END varchar(15),
OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','21:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','Y'

go


CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME --日期范围截止日期

AS
BEGIN

declare @emp_id varchar(10), @wk_st varchar(10), @wk_ed varchar(10), @ov_night varchar(15), @ot_ov_night varchar(2)
declare @ls_shift varchar(2), @st_punch varchar(15), @end_punch varchar(15), @st_ot varchar(15), @end_ot varchar(15)
declare @d int

select @d = datediff(dd, @Sdate, @Edate) + 1

create table #att_detail(
emp_no varchar(10),
T_date varchar(15),
shift_type varchar(2),
work_start datetime,
work_end datetime,
punch_start datetime,
punch_end datetime,
ot_start datetime,
ot_end datetime,
on_duty datetime,
off_duty datetime,
day_off int

)

Declare cur_emp cursor for
select emp_mst.emp_no, emp_mst.shift_type, shift_mst.work_start,
shift_mst.work_end, shift_mst.over_night, shift_mst.start_punch, shift_mst.stop_punch, shift_mst.ot_start,
shift_mst.ot_end, shift_mst.ot_ove_night
from emp_mst, shift_mst where emp_mst.shift_type = shift_mst.shift_type


open cur_emp
fetch from cur_emp into @emp_id, @ls_shift, @wk_st,
@wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
@end_ot, @ot_ov_night

while @@fetch_status = 0
begin

insert #att_detail (emp_no, t_date, shift_type, work_start,work_end, punch_start,punch_end, ot_start, ot_end, on_duty, off_duty,day_off)

select a.emp_no,
a.t_date ,
@ls_shift as shift_type,
cast(a.t_date + ' ' + @wk_st as datetime) as work_start,
case when @ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @wk_ed as datetime))
else cast(a.t_date + ' ' + @wk_ed as datetime) end work_end,
cast(a.t_date + ' ' + @st_punch as datetime) as punch_start,
cast(a.t_date + ' ' + @end_punch as datetime) as punch_end,
case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @st_ot as datetime))
else cast(a.t_date + ' ' + @st_ot as datetime) end st_ot,
case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @end_ot as datetime))
else cast(a.t_date + ' ' + @end_ot as datetime) end end_ot,
pucc_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between cast(a.t_date + ' ' + @st_punch as datetime) and cast(a.t_date + ' ' + @end_punch as datetime) ),
ot_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and dateadd(dd,1,@edate)) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between (case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @st_ot as datetime))
else cast(a.t_date + ' ' + @st_ot as datetime) end) and case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @end_ot as datetime))
else cast(a.t_date + ' ' + @end_ot as datetime) end ),
case when DATEPART(dw, a.t_date) in (select day_off from shift_sch_day where shift_type = @ls_shift) then 1 else 0 end day_off
from
(select distinct emp_no, T_date from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate)) a


fetch next from cur_emp into @emp_id, @ls_shift, @wk_st,
@wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
@end_ot, @ot_ov_night
end

close cur_emp
deallocate cur_emp

select emp_no, t_date, shift_type, work_start, work_end, punch_start, punch_end, ot_start, ot_end, on_duty, off_duty , case when work_start < on_duty then datediff(mi,work_start,on_duty) else 0 end as late_time, case when off_duty < work_end then datediff(mi,off_duty, work_end) else 0 end as early_time ,day_off from #att_detail


drop table #att_detail
End
go
EXEC SP_GetDetail '2004-04-01','2004-04-09'

select * from record order by emp_no, t_date, t_time

DROP TABLE RECORD, emp_mst, shift_mst,shift_sch_day
drop PROCEDURE SP_GetDetail

我已做出来了.但速度很慢.一个员工一个月的数据要一分钟.我公司有300多员工怎么办呀.
大家帮我想想,怎样修改要快点.多谢

[qianduo]

主要是这段花时间,大侠们快帮帮我呀
pucc_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and @edate) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between cast(a.t_date + ' ' + @st_punch as datetime) and cast(a.t_date + ' ' + @end_punch as datetime) ),
ot_t = ( select max(CAST(r.T_date + ' ' + LEFT(r.T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) from record r where (r.t_date + r.emp_no ) in (select (t_date + emp_no) from record where emp_no = @emp_id and (CAST(T_date AS datetime) between @Sdate and dateadd(dd,1,@edate)) ) and CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime) between (case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @st_ot as datetime))
else cast(a.t_date + ' ' + @st_ot as datetime) end) and case when @ot_ov_night ='Y' then dateadd(dd,1,cast(a.t_date + ' ' + @end_ot as datetime))
else cast(a.t_date + ' ' + @end_ot as datetime) end ),

[520zyb]

做过，但没时间帮你写得

帮顶！

[TaoTaoYou]

我正在做考勤系统　　不过我用的是access
实现功能：
1　签到　请假　出差
2　每天只能签到　请假　出差（三项中一次）否则数据不给予处理
3　记录每天签到时间　迟到时间　
4　月底系统自动算出本月实到几天　请假几天　出差几天
5　可自定义工资情况，系统自动核算每位员工的工资
　以上这些功能全部开发完毕　有需要的联系我　30458885

[qianduo]

多谢,上面的代码就是太慢了.找到了地方,大家看看怎么改,速度快点.