考勤统计

Question

select * from tablename a
where not exists (
select 1 from tablename
where CardNo=a.CardNo and HolderNo=a.HolderNo
and datediff(mi,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<5
)

Answer 1

select a.*,考勤次数=count(a.cardNo) from 考勤统计表名 where not exists(select 1 from 考勤统计表名 where CardNo=a.CardNo and IODate=a.IODate and IOTime>a.IOTime)

Answer 2

select * from tablename a
where not exists (
select 1 from tablename
where CardNo=a.CardNo and HolderNo=a.HolderNo and IODate=a.IODate
and datediff(mi,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<5
)

Answer 3

declare @t table(
CardNo       varchar(8),
HolderNo     varchar(8),
HolderName   varchar(8),
IODate       varchar(10),
IOTime       varchar(8),
IOGateNo     varchar(8),
IOGateName   varchar(12),
IOStatus     varchar(8),
DepartmentNo varchar(8))insert into @t select '0022','1131','刘耿艺','2006-01-12','12:56:43','01-1','北门进(进)','进入','0001'
insert into @t select '0034','0355','王红刚','2006-03-02','16:00:11','01-1','北门进(进)','进入','0001'
insert into @t select '0790','0963','杜国波','2006-03-02','16:02:13','01-8','北门出(出)','外出','0002'
insert into @t select '0790','0963','杜国波','2006-03-02','16:02:14','01-8','北门出(出)','外出','0002'
insert into @t select '1210','1190','苏涤飞','2006-03-02','16:02:16','01-8','北门出(出)','外出','0002'
insert into @t select '1210','1190','苏涤飞','2006-03-02','16:02:17','01-8','北门出(出)','外出','0002'
insert into @t select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
insert into @t select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
 select 
    distinct a.*
from
    @t a
where
    not exists(select 
                   1 
               from 
                   @t 
               where 
                   CardNo=a.CardNo and HolderNo=a.HolderNo 
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))>0
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<300)/*
CardNo   HolderNo HolderName IODate     IOTime   IOGateNo IOGateName   IOStatus DepartmentNo 
-------- -------- ---------- ---------- -------- -------- ------------ -------- ------------ 
0022     1131     刘耿艺     2006-01-12 12:56:43 01-1     北门进(进)    进入     0001
0034     0355     王红刚     2006-03-02 16:00:11 01-1     北门进(进)    进入     0001
0790     0963     杜国波     2006-03-02 16:02:13 01-8     北门出(出)    外出     0002
0792     0454     李晋芳     2006-03-02 16:02:21 01-8     北门出(出)    外出     0002
1210     1190     苏涤飞     2006-03-02 16:02:16 01-8     北门出(出)    外出     0002
*/

Answer 4

select * from tablename a
where not exists (
select 1 from tablename
where CardNo=a.CardNo and HolderNo=a.HolderNo and IODate=a.IODate
and datediff(mi,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<5
)

Answer 5

To :libin_ftsafe(子陌红尘) 
  真是佩服你驾驭SQL语句的能力，可以传授一点给我们吗？

Answer 6

create table test(
CardNo       varchar(8),
HolderNo     varchar(8),
HolderName   varchar(8),
IODate       varchar(10),
IOTime       varchar(8),
IOGateNo     varchar(8),
IOGateName   varchar(12),
IOStatus     varchar(8),
DepartmentNo varchar(8))insert into test select '0022','1131','刘耿艺','2006-01-12','12:56:43','01-1','北门进(进)','进入','0001'
insert into test select '0034','0355','王红刚','2006-03-02','16:00:11','01-1','北门进(进)','进入','0001'
insert into test select '0790','0963','杜国波','2006-03-02','16:02:13','01-8','北门出(出)','外出','0002'
insert into test select '0790','0963','杜国波','2006-03-02','16:02:14','01-8','北门出(出)','外出','0002'
insert into test select '1210','1190','苏涤飞','2006-03-02','16:02:16','01-8','北门出(出)','外出','0002'
insert into test select '1210','1190','苏涤飞','2006-03-02','16:02:17','01-8','北门出(出)','外出','0002'
insert into test select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
insert into test select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
 select 
    distinct a.*
into #
from
    test a
where
    not exists(select 
                   1 
               from 
                   test
               where 
                   CardNo=a.CardNo and HolderNo=a.HolderNo 
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))>0
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<300)
select
    a.HolderName,
    日期=day(a.IODate),
    时间1=max(case num when 1 then left(IOTime,5) end),
    时间2=max(case num when 2 then left(IOTime,5) end),
    时间3=max(case num when 3 then left(IOTime,5) end),
    时间4=max(case num when 4 then left(IOTime,5) end),
    时间5=max(case num when 5 then left(IOTime,5) end),
    时间6=max(case num when 6 then left(IOTime,5) end)
from
    (select b.*,(select count(*) from # where IODate=b.IODate and IOTime<=b.IOTime) as num from # b ) a
group by
    a.HolderName,day(a.IODate)/*
HolderName 日期        时间1    时间2     时间3    时间4    时间5     时间6      
---------- ----------- -------- -------- -------- -------- -------- -------- 
杜国波        2           NULL     16:02    NULL     NULL     NULL     NULL
李晋芳        2           NULL     NULL     NULL     16:02    NULL     NULL
苏涤飞        2           NULL     NULL     16:02    NULL     NULL     NULL
王红刚        2           16:00    NULL     NULL     NULL     NULL     NULL
刘耿艺        12          12:56    NULL     NULL     NULL     NULL     NULL
*/drop table test,#

Answer 7

create table test(
CardNo       varchar(8),
HolderNo     varchar(8),
HolderName   varchar(8),
IODate       varchar(10),
IOTime       varchar(8),
IOGateNo     varchar(8),
IOGateName   varchar(12),
IOStatus     varchar(8),
DepartmentNo varchar(8))insert into test select '0022','1131','刘耿艺','2006-01-12','12:56:43','01-1','北门进(进)','进入','0001'
insert into test select '0034','0355','王红刚','2006-03-02','16:00:11','01-1','北门进(进)','进入','0001'
insert into test select '0790','0963','杜国波','2006-03-02','16:02:13','01-8','北门出(出)','外出','0002'
insert into test select '0790','0963','杜国波','2006-03-02','16:02:14','01-8','北门出(出)','外出','0002'
insert into test select '1210','1190','苏涤飞','2006-03-02','16:02:16','01-8','北门出(出)','外出','0002'
insert into test select '1210','1190','苏涤飞','2006-03-02','16:02:17','01-8','北门出(出)','外出','0002'
insert into test select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
insert into test select '0792','0454','李晋芳','2006-03-02','16:02:21','01-8','北门出(出)','外出','0002'
 select 
    distinct a.*
into #
from
    test a
where
    not exists(select 
                   1 
               from 
                   test
               where 
                   CardNo=a.CardNo and HolderNo=a.HolderNo 
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))>0
                   and
                   datediff(ss,cast(IODate+' '+IOTime as datetime),cast(a.IODate+' '+a.IOTime as datetime))<300)
select
    a.HolderName,
    日期=day(a.IODate),
    时间1=max(case num when 1 then left(IOTime,5) end),
    时间2=max(case num when 2 then left(IOTime,5) end),
    时间3=max(case num when 3 then left(IOTime,5) end),
    时间4=max(case num when 4 then left(IOTime,5) end),
    时间5=max(case num when 5 then left(IOTime,5) end),
    时间6=max(case num when 6 then left(IOTime,5) end)
from
    (select b.*,(select count(*) from # where HolderName=b.HolderName and IODate=b.IODate and IOTime<=b.IOTime) as num from # b ) a
group by
    a.HolderName,day(a.IODate)/*
HolderName 日期        时间1    时间2     时间3    时间4    时间5     时间6      
---------- ----------- -------- -------- -------- -------- -------- -------- 
杜国波        2           16:02    NULL     NULL     NULL     NULL     NULL
李晋芳        2           16:02    NULL     NULL     NULL     NULL     NULL
苏涤飞        2           16:02    NULL     NULL     NULL     NULL     NULL
王红刚        2           16:00    NULL     NULL     NULL     NULL     NULL
刘耿艺        12          12:56    NULL     NULL     NULL     NULL     NULL
*/drop table test,#