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

Question

测试数据: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' unionselect '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' unionselect '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' 

Answer 1

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', 7create 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
BEGINdeclare @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 intselect  @d = datediff(dd, @Sdate, @Edate) + 1create 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_empselect 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_timeDROP TABLE RECORD, emp_mst, shift_mst,shift_sch_day
drop PROCEDURE SP_GetDetail我已做出来了.但速度很慢.一个员工一个月的数据要一分钟.我公司有300多员工怎么办呀.
大家帮我想想,怎样修改要快点.多谢

Answer 2

主要是这段花时间,大侠们快帮帮我呀
         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 ),

Answer 3

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