处理考勤打卡记录问题

EmployeeID=1 的 12号7:50记录用了两次
EmployeeID=3 的 12号4:00记录用了一次为什么？

解决方案 »

  1.   

    可能用update的速度不如用insert，就是先清空OnOffDutyData表（最好本来就空的）
      

  2.   

    4:00是凌晨打的卡，7:50的早上打的卡。很多工厂为了加班经常出现这样的差不多24小时上班情况。目前我使用游标计算方法只能按天处理，不能多天，因为游标太慢，没办法。先谢谢Yang_(扬帆破浪)的关注。
      

  3.   

    to Yang_(扬帆破浪):
    INSERT INTO 不是重要，主要是处理打卡数据，怎么样提高运行效率。
    找不到一个好的方法。
      

  4.   

    测试下以下笨方法的速度如何（特别注意：我直接删除了OriginalData标的数据，如果这时你的原始表，需要保存数据的，请用个临时表，并倒入OriginalData标的所有数据）update a
    set OnDuty1=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate)),
        OnDuty4=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=dateadd(day,1,a.CheckDate) and CheckTime<dateadd(day,2,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )update a
    set OffDuty1=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )update a
    set OnDuty2=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )update a
    set OffDuty2=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )update a
    set OnDuty3=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )update a
    set OffDuty3=(select top 1 checktime from OriginalData where EmployeeID=a.EmployeeID and CheckTime>=a.CheckDate and CheckTime<dateadd(day,1,a.CheckDate))
    from OnOffDutyData adelete a
    from OriginalData a
    where not exists (
    select 1 from OriginalData
    where EmployeeID=a.EmployeeID and 
    convert(Nchar(10),CheckTime,120)=convert(Nchar(10),a.CheckTime,120)
    and CheckTime<a.CheckTime
    )
      

  5.   

    --打卡记录表
    CREATE TABLE OriginalData([id] int IDENTITY(1,1),EmployeeID int,CheckTime datetime)
    INSERT INTO OriginalData
    SELECT 1,'2007-06-11 08:01' UNION ALL
    SELECT 1,'2007-06-11 12:02' UNION ALL
    SELECT 1,'2007-06-11 13:05' UNION ALL
    SELECT 1,'2007-06-11 17:40' UNION ALL
    SELECT 1,'2007-06-11 19:00' UNION ALL
    SELECT 1,'2007-06-11 23:42' UNION ALL
    SELECT 1,'2007-06-11 23:58' UNION ALL
    SELECT 1,'2007-06-12 07:50' UNION ALL
    SELECT 1,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-11 20:00' UNION ALL
    SELECT 3,'2007-06-12 04:00' UNION ALL
    SELECT 3,'2007-06-12 07:55' UNION ALL
    SELECT 3,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-12 13:00' UNION ALL
    SELECT 3,'2007-06-12 17:35' 
    --考勤表
    CREATE TABLE OnOffDutyData(EmployeeID int,CheckDate datetime
      ,OnDuty1 datetime,OffDuty1 datetime
    ,OnDuty2 datetime,OffDuty2 datetime
    ,OnDuty3 datetime,OffDuty3 datetime
    ,OnDuty4 datetime,OffDuty4 datetime)
    INSERT INTO OnOffDutyData (EmployeeID,CheckDate)
    SELECT 1,'2007-06-11' UNION ALL
    SELECT 1,'2007-06-12' UNION ALL
    SELECT 3,'2007-06-11' UNION ALL
    SELECT 3,'2007-06-12' declare @t table(EmployeeID int,CheckDate datetime,OnDuty1 varchar(10),OnDuty2 varchar(10),OnDuty3 varchar(10),OnDuty4 varchar(10),OnDuty5 varchar(10),OnDuty6 varchar(10),OnDuty7 varchar(10),OnDuty8 varchar(10))
    declare @lsb table(EmployeeID int,d datetime,m varchar(10),cnt int)insert @lsb
    SELECT  b.EmployeeID,d=convert(char(10),b.CheckTime,21),m=right(convert(char(16),b.CheckTime,21),5),
    Cnt=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and convert(char(10),b.CheckTime,21)=convert(char(10),CheckTime,21) and CheckTime<b.CheckTime )
    FROM  OriginalData binsert @t
    SELECT  a.EmployeeID,CheckDate=convert(char(10),a.CheckDate,21),
    OnDuty1=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 0 then m else null end),
    OnDuty1=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 1 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty2=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 2 then m else null end),
    OnDuty2=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 3 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty3=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 4 then m else null end),
    OnDuty3=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 5 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty4=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 6 then m else null end),
    OnDuty4=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 7 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end))
    FROM  OnOffDutyData a left join @lsb b on a.EmployeeID=b.EmployeeID
    group by a.EmployeeID,CheckDate
    order by a.EmployeeID,CheckDateselect  EmployeeID,CheckDate,
    OnDuty1,OnDuty2=case when OnDuty1 is not null then OnDuty2 else null end,
    OnDuty3,OnDuty4=case when OnDuty3 is not null then OnDuty4 else null end,
    OnDuty5,OnDuty6=case when OnDuty5 is not null then OnDuty6 else null end,
    OnDuty7,OnDuty8=case when OnDuty7 is not null then OnDuty8 else null end
    from @t
      

  6.   

    --打卡记录表
    CREATE TABLE OriginalData([id] int IDENTITY(1,1),EmployeeID int,CheckTime datetime)
    INSERT INTO OriginalData
    SELECT 1,'2007-06-11 08:01' UNION ALL
    SELECT 1,'2007-06-11 12:02' UNION ALL
    SELECT 1,'2007-06-11 13:05' UNION ALL
    SELECT 1,'2007-06-11 17:40' UNION ALL
    SELECT 1,'2007-06-11 19:00' UNION ALL
    SELECT 1,'2007-06-11 23:42' UNION ALL
    SELECT 1,'2007-06-11 23:58' UNION ALL
    SELECT 1,'2007-06-12 07:50' UNION ALL
    SELECT 1,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-11 20:00' UNION ALL
    SELECT 3,'2007-06-12 04:00' UNION ALL
    SELECT 3,'2007-06-12 07:55' UNION ALL
    SELECT 3,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-12 13:00' UNION ALL
    SELECT 3,'2007-06-12 17:35' 
    --考勤表
    CREATE TABLE OnOffDutyData(EmployeeID int,CheckDate datetime
      ,OnDuty1 datetime,OffDuty1 datetime
    ,OnDuty2 datetime,OffDuty2 datetime
    ,OnDuty3 datetime,OffDuty3 datetime
    ,OnDuty4 datetime,OffDuty4 datetime)
    INSERT INTO OnOffDutyData (EmployeeID,CheckDate)
    SELECT 1,'2007-06-11' UNION ALL
    SELECT 1,'2007-06-12' UNION ALL
    SELECT 3,'2007-06-11' UNION ALL
    SELECT 3,'2007-06-12' 
    declare @t table(EmployeeID int,CheckDate datetime,OnDuty1 varchar(10),OnDuty2 varchar(10),OnDuty3 varchar(10),OnDuty4 varchar(10),OnDuty5 varchar(10),OnDuty6 varchar(10),OnDuty7 varchar(10),OnDuty8 varchar(10))
    declare @lsb table(EmployeeID int,d datetime,m varchar(10),cnt int)insert @lsb
    SELECT  b.EmployeeID,d=convert(char(10),b.CheckTime,21),m=right(convert(char(16),b.CheckTime,21),5),
    Cnt=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and convert(char(10),b.CheckTime,21)=convert(char(10),CheckTime,21) and CheckTime<b.CheckTime )
    FROM  OriginalData binsert @t
    SELECT  a.EmployeeID,CheckDate=convert(char(10),a.CheckDate,21),
    OnDuty1=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 0 then m else null end),
    OnDuty1=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 1 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty2=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 2 then m else null end),
    OnDuty2=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 3 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty3=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 4 then m else null end),
    OnDuty3=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 5 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty4=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 6 then m else null end),
    OnDuty4=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 7 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end))
    FROM  OnOffDutyData a left join @lsb b on a.EmployeeID=b.EmployeeID
    group by a.EmployeeID,CheckDate
    order by a.EmployeeID,CheckDatedelete from OnOffDutyDatainsert OnOffDutyData
    select  EmployeeID,CheckDate,
    CheckDate+OnDuty1,OnDuty2=case when OnDuty1 is not null then CheckDate+OnDuty2 else null end,
    CheckDate+OnDuty3,OnDuty4=case when OnDuty3 is not null then CheckDate+OnDuty4 else null end,
    CheckDate+OnDuty5,OnDuty6=case when OnDuty5 is not null then CheckDate+OnDuty6 else null end,
    CheckDate+OnDuty7,OnDuty8=case when OnDuty7 is not null then CheckDate+OnDuty8 else null end
    from @tselect * from OnOffDutyData
      

  7.   

    to:
    leo_lesley(leo) ( ) 方法不错。我先测试，看看。希望还有更好的方法。再加100分 
      

  8.   

    ------------看看这个用的是两个表变量，然后直接update处理的，不用先删除，再插入。
    --打卡记录表
    CREATE TABLE OriginalData([id] int IDENTITY(1,1),EmployeeID int,CheckTime datetime)
    INSERT INTO OriginalData
    SELECT 1,'2007-06-11 08:01' UNION ALL
    SELECT 1,'2007-06-11 12:02' UNION ALL
    SELECT 1,'2007-06-11 13:05' UNION ALL
    SELECT 1,'2007-06-11 17:40' UNION ALL
    SELECT 1,'2007-06-11 19:00' UNION ALL
    SELECT 1,'2007-06-11 23:42' UNION ALL
    SELECT 1,'2007-06-11 23:58' UNION ALL
    SELECT 1,'2007-06-12 07:50' UNION ALL
    SELECT 1,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-11 20:00' UNION ALL
    SELECT 3,'2007-06-12 04:00' UNION ALL
    SELECT 3,'2007-06-12 07:55' UNION ALL
    SELECT 3,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-12 13:00' UNION ALL
    SELECT 3,'2007-06-12 17:35' go --考勤表
    CREATE TABLE OnOffDutyData(EmployeeID int,CheckDate datetime
     ,OnDuty1 datetime,OffDuty1 datetime
    ,OnDuty2 datetime,OffDuty2 datetime
    ,OnDuty3 datetime,OffDuty3 datetime
    ,OnDuty4 datetime,OffDuty4 datetime)
    INSERT INTO OnOffDutyData (EmployeeID,CheckDate)
    SELECT 1,'2007-06-11' UNION ALL
    SELECT 1,'2007-06-12' UNION ALL
    SELECT 3,'2007-06-11' UNION ALL
    SELECT 3,'2007-06-12' go declare @t table(EmployeeID int,CheckDate datetime,OnDuty1 varchar(10),OnDuty2 varchar(10),OnDuty3 varchar(10),OnDuty4 varchar(10),OnDuty5 varchar(10),OnDuty6 varchar(10),OnDuty7 varchar(10),OnDuty8 varchar(10))
    declare @lsb table(EmployeeID int,d datetime,m varchar(10),cnt int)insert @lsb
    SELECT b.EmployeeID,d=convert(char(10),b.CheckTime,21),m=right(convert(char(16),b.CheckTime,21),5),
    Cnt=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and convert(char(10),b.CheckTime,21)=convert(char(10),CheckTime,21) and CheckTime<b.CheckTime )
    FROM OriginalData binsert @t
    SELECT a.EmployeeID,CheckDate=convert(char(10),a.CheckDate,21),
    OnDuty1=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 0 then m else null end),
    OnDuty1=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 1 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty2=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 2 then m else null end),
    OnDuty2=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 3 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty3=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 4 then m else null end),
    OnDuty3=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 5 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty4=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 6 then m else null end),
    OnDuty4=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 7 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end))
    FROM OnOffDutyData a left join @lsb b on a.EmployeeID=b.EmployeeID
    group by a.EmployeeID,CheckDate
    order by a.EmployeeID,CheckDate
    update a 
    set  a.OnDuty1=t.CheckDate+t.OnDuty1,
    a.OffDuty1=case when t.OnDuty1 is not null then t.CheckDate+t.OnDuty2 else null end,
    a.OnDuty2=t.CheckDate+t.OnDuty3,
    a.OffDuty2=case when t.OnDuty3 is not null then t.CheckDate+t.OnDuty4 else null end,
    a.OnDuty3=t.CheckDate+t.OnDuty5,
    a.OffDuty3=case when t.OnDuty5 is not null then t.CheckDate+t.OnDuty6 else null end,
    a.OnDuty4=t.CheckDate+t.OnDuty7,
    a.OffDuty4=case when t.OnDuty7 is not null then t.CheckDate+t.OnDuty8 else null end
    from  OnOffDutyData a,@t t
    where  a.EmployeeID=t.EmployeeID and a.CheckDate=t.CheckDateselect * from OnOffDutyData
    drop table OnOffDutyData,OriginalData
      

  9.   

    非常感谢leo_lesley(leo)的帮助，方法不错，但还有一点小问题，已经标出问题点。
    我明天再看一下，怎么样改进。-------------------------------------------------------------打卡记录表
    CREATE TABLE OriginalData([id] int IDENTITY(1,1),EmployeeID int,CheckTime datetime)
    INSERT INTO OriginalData
    SELECT 1,'2007-06-11 08:01' UNION ALL
    SELECT 1,'2007-06-11 12:02' UNION ALL
    SELECT 1,'2007-06-11 13:05' UNION ALL
    SELECT 1,'2007-06-11 17:40' UNION ALL
    SELECT 1,'2007-06-11 19:00' UNION ALL
    --SELECT 1,'2007-06-11 23:42' UNION ALL --没有这句子，结果的第一行记录就有问题，无法记录第2天的第1次刷卡记录
    SELECT 1,'2007-06-11 23:58' UNION ALL
    SELECT 1,'2007-06-12 07:50' UNION ALL
    SELECT 1,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-11 20:00' UNION ALL
    SELECT 3,'2007-06-12 04:00' UNION ALL
    SELECT 3,'2007-06-12 07:55' UNION ALL
    SELECT 3,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-12 13:00' UNION ALL
    SELECT 3,'2007-06-12 17:35' go --考勤表
    CREATE TABLE OnOffDutyData(EmployeeID int,CheckDate datetime
     ,OnDuty1 datetime,OffDuty1 datetime
    ,OnDuty2 datetime,OffDuty2 datetime
    ,OnDuty3 datetime,OffDuty3 datetime
    ,OnDuty4 datetime,OffDuty4 datetime)
    INSERT INTO OnOffDutyData (EmployeeID,CheckDate)
    SELECT 1,'2007-06-11' UNION ALL
    SELECT 1,'2007-06-12' UNION ALL
    SELECT 3,'2007-06-11' UNION ALL
    SELECT 3,'2007-06-12' go declare @t table(EmployeeID int,CheckDate datetime,OnDuty1 varchar(10),OnDuty2 varchar(10),OnDuty3 varchar(10),OnDuty4 varchar(10),OnDuty5 varchar(10),OnDuty6 varchar(10),OnDuty7 varchar(10),OnDuty8 varchar(10))
    declare @lsb table(EmployeeID int,d datetime,m varchar(10),cnt int)insert @lsb
    SELECT b.EmployeeID,d=convert(char(10),b.CheckTime,21),m=right(convert(char(16),b.CheckTime,21),5),
    Cnt=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and convert(char(10),b.CheckTime,21)=convert(char(10),CheckTime,21) and CheckTime<b.CheckTime )
    FROM OriginalData binsert @t
    SELECT a.EmployeeID,CheckDate=convert(char(10),a.CheckDate,21),
    OnDuty1=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 0 then m else null end),
    OnDuty1=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 1 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty2=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 2 then m else null end),
    OnDuty2=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 3 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty3=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 4 then m else null end),
    OnDuty3=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 5 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end)),
    OnDuty4=max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 6 then m else null end),
    OnDuty4=isnull(max(case when a.EmployeeID=b.EmployeeID and convert(char(10),a.CheckDate,21)=d and cnt = 7 then m end),min(case when a.EmployeeID=b.EmployeeID and cnt = 0 and convert(char(10),a.CheckDate,21)<d then m end))
    FROM OnOffDutyData a left join @lsb b on a.EmployeeID=b.EmployeeIDgroup by a.EmployeeID,CheckDate
    order by a.EmployeeID,CheckDateSELECT * FROM @t
    update a 
    set  a.OnDuty1=t.CheckDate+t.OnDuty1,
    a.OffDuty1=case when t.OnDuty1 is not null then t.CheckDate+t.OnDuty2 else null end,
    a.OnDuty2=t.CheckDate+t.OnDuty3,
    a.OffDuty2=case when t.OnDuty3 is not null then t.CheckDate+t.OnDuty4 else null end,
    a.OnDuty3=t.CheckDate+t.OnDuty5,
    a.OffDuty3=case when t.OnDuty5 is not null then t.CheckDate+t.OnDuty6 else null end,
    a.OnDuty4=t.CheckDate+t.OnDuty7,
    a.OffDuty4=case when t.OnDuty7 is not null then t.CheckDate+t.OnDuty8 else null end
    from  OnOffDutyData a,@t t
    where  a.EmployeeID=t.EmployeeID and a.CheckDate=t.CheckDateselect * from OnOffDutyData
    drop table OnOffDutyData,OriginalData
      

  10.   

    --总体思想,先对时间进行排序,再就结果存到temp table中--打卡记录表
    CREATE TABLE OriginalData([id] int IDENTITY(1,1),EmployeeID int,CheckTime datetime)
    INSERT INTO OriginalData
    SELECT 1,'2007-06-11 08:01' UNION ALL
    SELECT 1,'2007-06-11 12:02' UNION ALL
    SELECT 1,'2007-06-11 13:05' UNION ALL
    SELECT 1,'2007-06-11 17:40' UNION ALL
    SELECT 1,'2007-06-11 19:00' UNION ALL
    SELECT 1,'2007-06-11 23:42' UNION ALL
    SELECT 1,'2007-06-11 23:58' UNION ALL
    SELECT 1,'2007-06-12 07:50' UNION ALL
    SELECT 1,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-11 20:00' UNION ALL
    SELECT 3,'2007-06-12 04:00' UNION ALL
    SELECT 3,'2007-06-12 07:55' UNION ALL
    SELECT 3,'2007-06-12 12:00' UNION ALL
    SELECT 3,'2007-06-12 13:00' UNION ALL
    SELECT 3,'2007-06-12 17:35' 
    --考勤表
    CREATE TABLE OnOffDutyData(EmployeeID int,CheckDate datetime
     ,OnDuty1 varchar(10),OffDuty1 varchar(10)
    ,OnDuty2 varchar(10),OffDuty2 varchar(10)
    ,OnDuty3 varchar(10),OffDuty3 varchar(10)
    ,OnDuty4 varchar(10),OffDuty4 varchar(10))
    INSERT INTO OnOffDutyData (EmployeeID,CheckDate)
    SELECT 1,'2007-06-11' UNION ALL
    SELECT 1,'2007-06-12' UNION ALL
    SELECT 3,'2007-06-11' UNION ALL
    SELECT 3,'2007-06-12' --SELECT * ,left(right(convert(varchar(23),checkTime,120),8),5) FROM OriginalData
    --SELECT * FROM OnOffDutyData
    -- update result
    update OnOffDutyData 
    set 
    onDuty1=(select top 1 left(right(convert(varchar(23),checkTime,120),8),5) FROM OriginalData b
    where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkTime)=0 order by checkTime )-- 先对时间进行排序,存到一个temp表中,提高查询速度
    select * ,layer=(select count(1) from OriginalData where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkTime)=0 and checkTime<=b.checkTime)
    into #temp from OriginalData b----最终结果
    select EmployeeID,CheckDate,
    OnDuty1=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=1),null),
    offDuty1=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=2),null),
    OnDuty2=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=3),null),
    offDuty2=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=4),null),
    OnDuty3=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=0 and layer=5),null),
    offDuty3=isnull((select left(right(convert(varchar(23),checkTime,120),8),5) from #temp where EmployeeID=b.EmployeeID and datediff(dd,checkTime,b.checkDate)=-1 and layer=1),null)
    from OnOffDutyData b--drop table
    drop table #temp
    drop table OnOffDutyData
    drop table OriginalData
      

  11.   

    SELECT EmployeeID,CheckTime,
    OnDuty1=max(OnDuty1),
    OnOffDuty1=ISNULL(max(OnOffDuty1),max(OnDuty4)),
    OnDuty2=case when max(OnOffDuty1) is null then NULL else ISNULL(max(OnDuty2),max(OnDuty4)) end,
    OnOffDuty2=case when max(OnDuty2) is null then NULL else ISNULL(max(OnOffDuty2),max(OnDuty4)) end,
    OnDuty3=case when max(OnOffDuty2) is null then NULL else ISNULL(max(OnDuty3),max(OnDuty4)) end,
    OnOffDuty3=case when max(OnDuty3) is null then NULL else ISNULL(max(OnOffDuty3),max(OnDuty4)) end,
    OnDuty4=case when max(OnOffDuty3) is null then NULL else max(OnDuty4) end
    FROM
      

  12.   

    (select EmployeeID,CheckTime=convert(varchar(10),CheckTime,120),
    OnDuty1=(select top 1 convert(varchar(5),CheckTime,108) from @OriginalData where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 order by CheckTime),
    OnOffDuty1=(select convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID  and datediff(dd,CheckTime,t.CheckTime) = 0 and
    (select count(*) from @OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 2),
    OnDuty2=(select convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
    (select count(*) from @OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 3),
      

  13.   

    OnOffDuty2=(select convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
    (select count(*) from @OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 4),
    OnDuty3=(select convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
    (select count(*) from @OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 5),
    OnOffDuty3=(select convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,CheckTime,t.CheckTime) = 0 and
    (select count(*) from @OriginalData where EmployeeID = a.EmployeeID and datediff(dd,CheckTime,a.CheckTime) = 0 and CheckTime <= a.CheckTime)= 6),
      

  14.   

    OnDuty4=(select top 1 convert(varchar(5),CheckTime,108) from @OriginalData as a where EmployeeID = t.EmployeeID and datediff(dd,t.CheckTime,CheckTime)=1 order by CheckTime)
    from @OriginalData as t ) AS x 
    GROUP BY EmployeeID,CheckTime ORDER BY 1,2
    --请楼主把上面三段SQL连起来.我这里无法把完整的帖出来(无法发送),只好拆开,不知道CSDN又怎么了.
      

  15.   

    结果:
    EmployeeID  CheckTime  OnDuty1 OnOffDuty1 OnDuty2 OnOffDuty2 OnDuty3 OnOffDuty3 OnDuty4 
    ----------- ---------- ------- ---------- ------- ---------- ------- ---------- ------- 
    1           2007-06-11 08:01   12:02      13:05   17:40      19:00   23:42      07:50
    1           2007-06-12 07:50   12:00      NULL    NULL       NULL    NULL       NULL
    3           2007-06-11 20:00   04:00      NULL    NULL       NULL    NULL       NULL
    3           2007-06-12 04:00   07:55      12:00   13:00      17:35   NULL       NULL
      

  16.   

    1.truncate table 
    2.修改考勤表，增加刷卡记录标记
    ３按标记依次更新update
    以上意见仅供参考