高分请教一个人事出勤档案处理的问题，急！！！！！！！！！！！！！！

[verious]

请教一个人事出勤档案处理的问题:

两张数据库表: A,B

描述：A,B两表分别为出勤明细表和考勤记录表

A表结构为:perid,date1,time
B表结构为:perid,date2,k1,k2,k3,k4,k5,k6

数据处理方案：以A表为基准，每新增一条出勤记录后，搜寻B表相关的记录(搜寻B表中与A表中人事编号

perid和出勤日期date相符的记录)，将出勤时间time按时间段插入B表的k(?)中


现求该处理方案的触发器编程或者存储过程编程。

以下是我写的一个触发器，不过处理结果并不好，哪位大大帮个忙阿~~~~~~

CREATE trigger chuqin
on 指纹打卡
for insert
as

declare @perid varchar(20)
declare @date1 datetime
declare @time1 varchar(5)

begin

select @perid=人事编号,@date1=日期,@time1=时间 from 指纹打卡

if datepart(hh,convert(smalldatetime,@time1))>=5 and datepart(hh,convert

(smalldatetime,@time1))<9
update 考勤记录 set 刷卡2=@time1 where 刷卡日期=@date1 and 人事编号=@perid

end

[晓风残月0110]

up up 接分，接分

[ojuju10]

每天只刷卡6次吗？

[dawugui]

--显示
select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1

--更新
update b
set k1 = n.k1,k2 = n.k2,k3 = n.k3,k4 = n.k4,k5 = n.k5,k6 = n.k6
from b,(select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1) n
where b.perid = n.perid and b.date2 = n.date1

[dawugui]

create table a(perid int,date1 varchar(10),time varchar(10))
insert into a values(1,'2007-10-11','01:00')
insert into a values(1,'2007-10-11','02:00')
insert into a values(1,'2007-10-11','03:00')
insert into a values(1,'2007-10-11','04:00')
insert into a values(1,'2007-10-11','05:00')
insert into a values(1,'2007-10-11','06:00')
insert into a values(1,'2007-10-12','07:00')
insert into a values(1,'2007-10-12','08:00')
insert into a values(1,'2007-10-12','09:00')
insert into a values(1,'2007-10-12','10:00')
insert into a values(1,'2007-10-12','11:00')
insert into a values(1,'2007-10-12','12:00')
create table b(perid int,date2 varchar(10),k1 varchar(10),k2 varchar(10),k3 varchar(10),k4 varchar(10),k5 varchar(10),k6 varchar(10))
insert into b values(1,'2007-10-11',null,null,null,null,null,null)
insert into b values(1,'2007-10-12',null,null,null,null,null,null)
go
--a表数据
select * from a
/*
perid date1 time
----------- ---------- ----------
1 2007-10-11 01:00
1 2007-10-11 02:00
1 2007-10-11 03:00
1 2007-10-11 04:00
1 2007-10-11 05:00
1 2007-10-11 06:00
1 2007-10-12 07:00
1 2007-10-12 08:00
1 2007-10-12 09:00
1 2007-10-12 10:00
1 2007-10-12 11:00
1 2007-10-12 12:00
（所影响的行数为 12 行）
*/

--b表数据
select * from b
/*
perid date2 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 NULL NULL NULL NULL NULL NULL
1 2007-10-12 NULL NULL NULL NULL NULL NULL
（所影响的行数为 2 行）
*/

--显示结果
select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1
/*
perid date1 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 01:00 02:00 03:00 04:00 05:00 06:00
1 2007-10-12 07:00 08:00 09:00 10:00 11:00 12:00
（所影响的行数为 2 行）
*/
--更新

update b
set k1 = n.k1,k2 = n.k2,k3 = n.k3,k4 = n.k4,k5 = n.k5,k6 = n.k6
from b,(select perid,date1,
max(case px when 1 then time end) k1,
max(case px when 2 then time end) k2,
max(case px when 3 then time end) k3,
max(case px when 4 then time end) k4,
max(case px when 5 then time end) k5,
max(case px when 6 then time end) k6
from
(
select px=(select count(1) from A where perid = t.perid and date1 = t.date1 and time<t.time)+1 , * from a t
) m
group by perid,date1) n
where b.perid = n.perid and b.date2 = n.date1
select * from b
/*
perid date2 k1 k2 k3 k4 k5 k6
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2007-10-11 01:00 02:00 03:00 04:00 05:00 06:00
1 2007-10-12 07:00 08:00 09:00 10:00 11:00 12:00
（所影响的行数为 2 行）
*/
drop table a,b

[dawugui]

我这里用的是字符串,如果是时间型,你自己改改即可,另把你>=5,<=9的条件加在where后面就行了.

[verious]

谢谢哈，马上看看，我那个时间段也是字符的

[verious]

基本解决了，刚有朋友修改了一下我的触发器，现在可以更新了^_^


CREATE trigger chuqin
on dbo.指纹打卡
for insert
as

declare @perid varchar(20)
declare @date1 datetime
declare @time1 varchar(5)

begin

select @perid=人事编号,@date1=日期,@time1=时间 from inserted

if datepart(hh,convert(smalldatetime,@time1))>=0 and datepart(hh,convert(smalldatetime,@time1))<5
update 考勤记录 set 刷卡6=a2.时间 from 考勤记录 a1,inserted a2 where a1.刷卡日期=a2.日期 and a1.人事编号=a2.人事编号



end

[verious]

怎么现在无法即时给分了？