考勤问题

表1 部门、姓名、工号、班别名称、早卡、午卡、晚卡、日期、星期
    
特别说明
班别编码   班别名称 早卡 午卡 晚卡 日期 星期
01 正常班 8:00 13:30 17:30 2011-10-01 星期六
01 正常班 8:00 13:30 17:30 2011-10-03 星期一
01 正常班 8:00 13:30 17:30 2011-10-04 星期二
01 正常班 8:00 13:30 17:30 2011-10-05 星期三
01 正常班 8:00 13:30 17:30 2011-10-06 星期四
01 正常班 8:00 13:30 17:30 2011-10-07 星期五
01 正常班 8:00 13:30 17:30 2011-10-08 星期六
01 正常班 8:00 13:30 17:30 2011-10-09 星期日
01 正常班 8:00 13:30 17:30 2011-10-10 星期一
01 正常班 8:00 13:30 17:30 2011-10-11 星期二
01 正常班 8:00 13:30 17:30 2011-10-12 星期三
01 正常班 8:00 13:30 17:30 2011-10-13 星期四
01 正常班 8:00 13:30 17:30 2011-10-14 星期五
班次有这几种：
班别编码 班别名称 早卡 午卡 晚卡 日期 星期
01 正常班 8:00 13:30 17:30 2011-10-01 星期六
02 早班 7:30 13:30 17:30 2011-10-03 星期一
03 中班 10:00 12:00 18:00 2011-10-04 星期二
04 晚班 15:00 18:00 21:30 2011-10-05 星期三表2 姓名、工号、考勤号、考勤设备号特别说明：
工号 姓名 考勤号   设备号
152 XXX001 73         1
110 XXX002 199        1 
162 XXX003 118        1
7 XXX004   14         1
表3 考勤号、考勤设备号、打开时间
   特别说明：
考勤设备号、勤号、打开时间1 1 2011-09-01 21:24:30.000
1 1 2011-09-02 08:03:04.000
1 1 2011-09-02 22:17:22.000
1 1 2011-09-03 08:09:44.000
1 1 2011-09-03 19:10:10.000
1 1 2011-09-06 08:01:20.000
1 1 2011-09-06 22:07:37.000
1 1 2011-09-07 08:01:11.000
前几天有发过类似帖子，没有带数据。
今天就戴上数据，比较直观的让大家帮助我、
上面是一些实际数据，我想根据这些数据写一个考勤的存储过程根据不同班次判断每个人打开时间是否有效。（我们的考勤计算到分钟。比如：早上8点上班，8:00:59 也算正常打卡）最后想要的结果是：姓名  部门   正常考勤天数（周一-周五） 周六考勤天数 周日加班
XXX   行政部    一天正常打3次，算1天正常出勤  请高手给想想怎么写！小弟感激不尽！    

解决方案 »

  1.   


    select a.姓名,a.部门,
    count(case when a.星期 in ('星期一','星期二','星期三','星期四','星期五') and count(c.打开时间)=3 then 1 else 0 end) '正常考勤天数',
    count(case when a.星期 in ('星期六') and count(c.打开时间)=3 then 1 else 0 end) '周六考勤天数',
    count(case when a.星期 in ('星期日') and count(c.打开时间)=3 then 1 else 0 end) '周日加班'
    from 表1 a
    inner join 表2 b on a.工号=b.工号
    inner join 表3 c on datediff(d,a.日期,c.打开时间)=0 and b.考勤设备号=c.考勤设备号
    group by a.工号有个问题喔,表3的打开时间,怎么知道是对应哪个班别的呢?
      

  2.   

    我估计这个得写程序来慢慢整,只用SQL语句怕是难哦.
      

  3.   


    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Punch_Time_IsEffective_By_Emp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[Punch_Time_IsEffective_By_Emp]
    GO -- Procedure
    -- Procedure -- =============================================
    -- Create date: <2011/10/16>
    -- Description: <根据不同班次判断每个人打开时间是否有效>
    -- =============================================
    CREATE PROCEDURE [dbo].[Punch_Time_IsEffective_By_Emp]
    @Start_Datetime datetime, --考勤开始时间
    @End_Datetime datetime --考勤结束时间
    as
    begin declare @Sql_Str nvarchar(2000) --Sql语句
    declare @Emp_id nvarchar(36) --工号
    declare @Emp_Count int --记录数
    declare @Morning_Card_Datetime datetime --早卡时间
    declare @Noon_Card_Datetime datetime --午卡时间
    declare @Night_Card_Datetime datetime --晚卡时间
    declare @Att_Datetime datetime --打卡时间

    --创建一临时表将计算结果更新到临时表
    create table #Result_Tb(Emp_Id nvarchar(36),Emp_Dep nvarchar(36),Emp_Att_Datetime datetime,Emp_Info nvarchar(36))


    --将考勤期段内的工号，部门插入临时表，计算结果根据工号更新临时表的考勤时间和考勤结果
    set @Sql_Str='insert into #Result_Tb (Emp_Id,Emp_Dep,Emp_Att_Datetime,Emp_Info) select emp_id,部门名称,null,null from tb1 
    where datediff(day,日期,'''+Convert(nvarchar(36),@Start_Datetime)+')<0 and datediff(day,日期,'''+Convert(nvarchar(36),@End_Datetime)+')>0'
    exec(@Sql_Str)

    while(@Start_Datetime<=@End_Datetime)  --循环每一天处理每一个员工
    begin --循环考勤的时间开始 set @Sql_Str='select @Emp_Count=count(*) from tb1 where datediff(day,日期,'''+convert(nvarchar(36),@Start_Datetime)+''')=0'
    exec sp_executesql @sql_Str ,N'@Emp_Count int out' ,@Emp_Count out
    set @Emp_Count=isnull(@Emp_Count,0)
    if(@Emp_Count>0)
    begin
    set @Sql_str = 'declare Cursor_Get_In_Or_Out cursor SCROLL  DYNAMIC for select emp_id,早卡，午卡，晚卡,from tb1 
    where datediff(day,日期,'''+convert(nvarchar(36),@Start_Datetime)+''')'
    exec (@Sql_str) 
    open Cursor_Emp_DateTime   
    fetch next from Cursor_Emp_DateTime  into @Emp_id,@Morning_Card_Datetime,@Noon_Card_Datetime,@Night_Card_Datetime
    while(@@fetch_status=0) --游标循环开始
    begin
    --创建临时表保存当前这个人的打卡记录
    create table #Emp_EverOne_TB(Emp_Card nvarchar(36),Emp_Att_DateTime datetime)

    --根据工号emp_id在tb2中找到考勤号关联tb3找到该员工该天的打卡记录插入临时表
    set @Sql_Str='insert into #Emp_EverOne_TB(Emp_Card,Emp_Att_DateTime) select 考勤号,打卡时间 from tb3 as a left join tb2 as b on a.考勤号=b.考勤号 
    where b.emp_id='''+@Emp_id+''' and datediff(day,a.打卡时间,'''+Convert(nvarchar(36),@Start_Datetime)+''')=0 order by a.打卡时间'
    exec(@Sql_Str) --top1取的第一条数据是早上的打卡记录，和该员工的班次对应的早卡时间作对比
    set @Sql_Str='select top 1 @Att_Datetime=Emp_Att_DateTime from #Emp_EverOne_TB'
    exec sp_executesql @sql_Str ,N'@Att_Datetime datetime out' ,@Att_Datetime out
    set @Att_Datetime=isnull(@Att_Datetime,'0:00') --判断员工实际打卡时间和早卡时间是不是在同一时间
    if datediff(mi,@Att_Datetime,dateadd(ss,59,@Morning_Card_Datetime))>=0 --实际打卡时间应该在规定时间+59秒之前打卡才算正常
    begin
    --在同一时间，说明早卡正常，删除临时表中的早卡记录，在取top1得到午卡时间
    set @Sql_Str='delete #Emp_EverOne_TB where datetdiff(ss,Emp_Att_DateTime,'''+Convert(nvarchar(36),@Att_Datetime)+''')=0'
    exec(@Sql_Str)
    set @Sql_Str='select top 1 @Att_Datetime=Emp_Att_DateTime from #Emp_EverOne_TB'
    exec sp_executesql @sql_Str ,N'@Att_Datetime datetime out' ,@Att_Datetime out
    set @Att_Datetime=isnull(@Att_Datetime,'0:00')
    if datediff(mi,@Att_Datetime,dateadd(ss,59,@Noon_Card_Datetime))>=0 --实际打卡时间应该在规定时间+59秒之前打卡才算正常
    begin
    --在同一时间，删除临时表中的午卡记录，在取top1得到晚卡时间
    set @Sql_Str='delete #Emp_EverOne_TB where datetdiff(ss,Emp_Att_DateTime,'''+Convert(nvarchar(36),@Att_Datetime)+''')=0'
    exec(@Sql_Str)
    set @Sql_Str='select top 1 @Att_Datetime=Emp_Att_DateTime from #Emp_EverOne_TB'
    exec sp_executesql @sql_Str ,N'@Att_Datetime datetime out' ,@Att_Datetime out
    set @Att_Datetime=isnull(@Att_Datetime,'0:00')
    if datediff(mi,@Att_Datetime,dateadd(ss,59,@Night_Card_Datetime))>=0 --实际打卡时间应该在规定时间+59秒之前打卡才算正常
    begin
    --早卡，午卡，晚卡时间都正确，更新#Result_Tb表
    set @Sql_Str='update #Result_Tb set Emp_Att_Datetime='''+convert(nvarchar(36),@Start_Datetime)+''',Emp_Info=''正常出勤'' where emp_id='''+@emp_id+''''
    exec(@Sql_Str)
    end
    else
    begin
    set @Sql_Str='update #Result_Tb set Emp_Att_Datetime='''+convert(nvarchar(36),@Start_Datetime)+''',Emp_Info=''不正常出勤'' where emp_id='''+@emp_id+''''
    exec(@Sql_Str)
    end
    end
    else
    begin
    set @Sql_Str='update #Result_Tb set Emp_Att_Datetime='''+convert(nvarchar(36),@Start_Datetime)+''',Emp_Info=''不正常出勤'' where emp_id='''+@emp_id+''''
    exec(@Sql_Str)
    end
    end
    else
    begin
    set @Sql_Str='update #Result_Tb set Emp_Att_Datetime='''+convert(nvarchar(36),@Start_Datetime)+''',Emp_Info=''不正常出勤'' where emp_id='''+@emp_id+''''
    exec(@Sql_Str)
    end
    drop table #Emp_EverOne_TB --删除临时表
    fetch next from Cursor_Emp_DateTime  into @Emp_id,@Morning_Card_Datetime,@Noon_Card_Datetime,@Night_Card_Datetime
    end --游标循环结束
    close Cursor_Get_In_Or_Out   
    deallocate Cursor_Get_In_Or_Out 
    set @Start_Datetime=dateadd(day,1,@Start_Datetime)
    end--循环考勤的时间结束
    end
    end/*看你的意思不是很清楚，但是里面问题应该很多的，表2和表3是根据考勤号关联，为什么都还有设备号
    表三中的时间，应该还要看是进门，还是出门吧，应该是根据设备去辨别吧，光有时间怎么可能知道是出还是进呢*/