考勤记录 如下：
部门 姓名 考勤号码 日期时间 记录状态 机器号 编号
办公室 张宗昌 1 2015-1-4 07:53:15 上班签到 1 指纹
办公室 张宗昌 1 2015-1-4 12:02:15 下班签到 1 指纹
排班也是比较简单，设置了上班时间和下班时间
例如：
time_start /end_time
8：00/12：00
大家说一下思路，
怎么能在统计报表中体现出来，8点之后来算迟到，12之前走算早退呢？？
怎么对比？脑子比较乱 ，实在整不出来啦。
------解决思路----------------------
修改... SQL Server 2000环境测试通过！'
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.attendance') is not null drop table attendance
-- 创建数据表
create table attendance
(
name char(5),
id int,
time datetime,
type char(20)
)
go
--插入测试数据
insert into attendance select '张三',59775623,'2010-04-01 07:23:37',null
union all select '张三',59775623,'2010-04-01 07:50:21',null
union all select '张三',59775623,'2010-04-01 18:20:22',null
union all select '张三',59775623,'2010-04-01 18:50:53',null
union all select '李四',59775624,'2010-04-01 07:00:06',null
union all select '李四',59775624,'2010-04-01 18:00:12',null
union all select '李四',59775624,'2010-04-02 08:20:32',null
union all select '李四',59775624,'2010-04-02 17:00:22',null
union all select '李四',59775624,'2010-04-02 18:18:08',null
union all select '王五',59775625,'2010-04-01 08:02:06',null
union all select '王五',59775625,'2010-04-01 18:00:12',null
union all select '王五',59775625,'2010-04-02 07:20:32',null
union all select '王五',59775625,'2010-04-02 12:35:22',null
union all select '王五',59775625,'2010-04-02 18:18:08',null
go
--代码实现
declare @temptb table(name char(5),id int,_time datetime,time varchar(5),type char(20),idd int)
-->更新数据
insert into @temptb
select name,id,_time=min(_time),time=min(time),type,idd from
(
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=1
from attendance t where convert(varchar(5),time,8)<='12:00'
)t
group by name,id,convert(varchar(10),_time,120),type,idd
union all
select name,id,_time=min(_time),time=min(time),type,idd from
(
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=1
from attendance t where convert(varchar(5),time,8)>='13:00'
)t
group by name,id,convert(varchar(10),_time,120),type,idd
union all
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=0
from attendance where convert(varchar(5),time,8)>='12:00' and convert(varchar(5),time,8)<='13:00'
update attendance set type=t2.type
from attendance t1
inner join
(
select name,id,time=_time,type=case when time<='08:00' and idd=1 then '上班'
when time>'08:00' and time<='12:00' and idd=1 then '迟到'
when time<'12:00' and idd=2 then '上班重复刷卡'
when time>='13:00' and time<='17:30' and idd=1 then '早退'
when time>'17:30' and idd=1 then '下班'
when time>'13:00' and idd=2 then '下班重复刷卡'
when idd=0 then '乱刷卡' end
from
(
select * from @temptb
union all
select name,id,_time=time,time=convert(varchar(5),time,8),type,idd=2 from attendance t
where not exists (select 1 from @temptb where id=t.id and _time=t.time)
)t
) t2
on t1.id=t2.id and t1.time=t2.time
-->显示更新后数据
select * from attendance
/*测试结果
name id time type
--------------------------------------------------------------
张三 59775623 2010-04-01 07:23:37.000 上班
张三 59775623 2010-04-01 07:50:21.000 上班重复刷卡
张三 59775623 2010-04-01 18:20:22.000 下班
张三 59775623 2010-04-01 18:50:53.000 下班重复刷卡
李四 59775624 2010-04-01 07:00:06.000 上班
李四 59775624 2010-04-01 18:00:12.000 下班
李四 59775624 2010-04-02 08:20:32.000 迟到
李四 59775624 2010-04-02 17:00:22.000 早退
李四 59775624 2010-04-02 18:18:08.000 下班重复刷卡
王五 59775625 2010-04-01 08:02:06.000 迟到
王五 59775625 2010-04-01 18:00:12.000 下班
王五 59775625 2010-04-02 07:20:32.000 上班
王五 59775625 2010-04-02 12:35:22.000 乱刷卡
王五 59775625 2010-04-02 18:18:08.000 下班
(14 行受影响)
*/