--------------------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
--代码实现
-->更新数据
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<>1
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<>1
then
'下班重复刷卡'
when
time
>=
'12:00'
and
time
<=
'13:00'
then
'乱刷卡'
end
from
(
select
name
,id,_time=
time
,
time
=
convert
(
varchar
(5),
time
,8),type,
idd=row_number()over(partition
by
convert
(
varchar
(10),
time
,120),
name
order
by
time
)
from
attendance
where
convert
(
varchar
(5),
time
,8)<=
'12:00'
union
all
select
name
,id,_time=
time
,
time
=
convert
(
varchar
(5),
time
,8),type,
idd=row_number()over(partition
by
convert
(
varchar
(10),
time
,120),
name
order
by
time
)
from
attendance
where
convert
(
varchar
(5),
time
,8)>=
'13:00'
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'
)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 行受影响)
*/