考勤统计

netlimitless 2006-05-17 02:48:33
CardNo HolderNo HolderName IODate IOTime IOGateNo IOGateName IOStatus DepartmentNo
0022 1131 刘耿艺 2006-1-12 12:56:43 01-1 北门进(进) 进入 0001
0034 0355 王红刚 2006-3-2 16:00:11 01-1 北门进(进) 进入 0001
0790 0963 杜国波 2006-3-2 16:02:13 01-8 北门出(出) 外出 0002
0790 0963 杜国波 2006-3-2 16:02:14 01-8 北门出(出) 外出 0002
1210 1190 苏涤飞 2006-3-2 16:02:16 01-8 北门出(出) 外出 0002
1210 1190 苏涤飞 2006-3-2 16:02:17 01-8 北门出(出) 外出 0002
0792 0454 李晋芳 2006-3-2 16:02:21 01-8 北门出(出) 外出 0002
0792 0454 李晋芳 2006-3-2 16:02:21 01-8 北门出(出) 外出 0002

按天取得 考勤次数 （时间相差5分钟的 取早的一次）
...全文
239 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
子陌红尘 2006-05-22
  • 打赏
  • 举报
 回复
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,#
子陌红尘 2006-05-22
  • 打赏
  • 举报
 回复
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,#
netlimitless 2006-05-21
  • 打赏
  • 举报
 回复
上面的是要 要的打印结果

现在只要实现 如下即可
|------|------|------|------|------|-----|------|------|
|姓名 |日期 |时间1 |时间2 |时间3 |时间4| 时间5| 时间6|
|------|------|------|------|------|-----|------|------|
| 张某 | 1 |8:00 | 12:00| 14:00|16:00| | |
|------|------|------|------|------|-----|------|------|

没有的为空
netlimitless 2006-05-21
  • 打赏
  • 举报
 回复
想得到的结果
|------|------|------|------|------|-----|------|
|姓名 | 1 | 2 | 3 | 4 |.....| 31 |
|------|------|------|------|------|-----|------|
|张某 | 8:00 | 8:00 | 8:00 | 8:03 |.....| 8:05 |
| |12:00 |12:00 |12:00 |12:03 |.....|12:05 |
| |14:00 |14:00 |14:00 |14:03 |.....|14:05 |
| |18:00 |18:00 |18:00 |18:03 |.....|18:05 |
|------|------|------|------|------|-----|------|
| . | . | . | . | . | . | . |
|------|------|------|------|------|-----|------|
| . | . | . | . | . | . | . |
|------|------|------|------|------|-----|------|
|李某 | 8:00 | 8:00 | 8:00 | 8:03 |.....| 8:05 |
| |12:00 |12:00 |12:00 |12:03 |.....|12:05 |
| |14:00 |14:00 |14:00 |14:03 |.....|14:05 |
| |18:00 |18:00 |18:00 |18:03 |.....|18:05 |
|------|------|------|------|------|-----|------|
netlimitless 2006-05-20
  • 打赏
  • 举报
 回复
谢谢你们了， 我看看能不能用
Name 2006-05-17
  • 打赏
  • 举报
 回复
To :libin_ftsafe(子陌红尘)
真是佩服你驾驭SQL语句的能力，可以传授一点给我们吗？
quanyi 2006-05-17
  • 打赏
  • 举报
 回复
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
)
子陌红尘 2006-05-17
  • 打赏
  • 举报
 回复
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
*/
昵称被占用了 2006-05-17
  • 打赏
  • 举报
 回复
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
)
itblog 2006-05-17
  • 打赏
  • 举报
 回复
select a.*,考勤次数=count(a.cardNo) from 考勤统计表名 where not exists(select 1 from 考勤统计表名 where CardNo=a.CardNo and IODate=a.IODate and IOTime>a.IOTime)
昵称被占用了 2006-05-17
  • 打赏
  • 举报
 回复
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
)
37_月度考勤统计表考勤相关资料.doc
37_月度考勤统计表考勤相关资料.doc
30员工月度考勤统计表.zip
30员工月度考勤统计表
14289 员工月度考勤统计表.zip
14289 员工月度考勤统计表.zip
员工考勤统计软件
可以根据指纹机中员工的打卡情况对员工的加班、迟到、请假等考勤情况进行统计。
vba模块2生成考勤表_考勤机记录生成考勤统计报告_
根据考勤机导出的考勤明细记录，生成考勤统计报告
疑难问题

22,210

社区成员

121,729

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章