最近写的三个关于考勤的存储过程
前一阵做一个考勤记录的东东，自己写了三个存储过程，记录如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: 张玉丰
— Create date: 2008/8/13
— Description: 用于输出考勤记录的排行榜
— =============================================
ALTER PROCEDURE [dbo].[attendListTime]
(
@sDate datetime,
@eDate Datetime
)
AS
BEGIN
declare @Aid int
declare @zCount int
declare @Count int
declare @TotalTime int
declare @ACount int
declare @username nvarchar(256)
SET NOCOUNT ON;
create table #zyf_Show
(
username nvarchar(256),
Atime float,
Acount int
)
select username into #zyf_Name from dbo.aspnet_Users
set @zCount=(select count(*) from #zyf_Name)
while (@zCount>0)
begin
set @username=(select top 1 username from #zyf_Name)
set @TotalTime=0
select * into #zyf_temp from Attend
where (UserName=@username) and
(AttendTime between @sDate and @eDate) and isActive=0
set @Count=(select count(*) from #zyf_temp)
set @ACount=@Count
while (@Count>0)
begin
set @Aid=(select min(Attendid) from #zyf_temp)
set @TotalTime=@TotalTime+(select datediff(minute,(select InTime from #zyf_temp where AttendId=@Aid),(select OutTime from #zyf_temp where AttendId=@Aid)))
delete from #zyf_temp where AttendId=@Aid
set @Count=(select count(*) from #zyf_temp)
end
drop table #zyf_temp
—select @TotalTime as ok,@Aid as AID,@iTry as Try
insert into #zyf_Show(username,Atime,Acount) values(@username,@TotalTime,@ACount)
delete from #zyf_Name where username=@username
set @zCount=@zCount–1
end
select * from #zyf_Show order by Atime desc
drop table #zyf_Show
drop table #zyf_Name
END
第一个：记录考勤
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4ALTER PROCEDURE [dbo].[DS_Attend]
5 (@username nvarchar(256),
6 @action varchar(10))
7AS
8
/**//*功能： 本存储过程实现成员的考勤的记录功能，及考勤时间计算功能。 */
9/**//*作者：张玉丰 */
10/**//*日期：2007-8-15 */
11
12if @action=\’in\’
13begin
14 insert into Attend(UserName,InTime) values(@UserName,getdate())
15 update UserInfo set UIsInCenter=1 where UserName=@username
16
17end
18
19if @action=\’out\’
20begin
21 declare @startTime datetime —本次签入时间；
22 declare @oldTime float —成员累计时长；
23 set @oldTime=(select UinCenterTime from UserInfo where UserName=@username )
24 set @startTime=(select InTime from Attend where UserName=@username and IsActive=1)
25 update UserInfo set UinCenterTime=@oldTime+datediff(minute,@startTime,getdate())
26 where
27 UserName=@username
28 update Attend set OutTime=getdate(),IsActive=0 where UserName=@username and IsActive=1
29
30
31
32 update UserInfo set UIsInCenter=0 where UserName=@username
33
34end
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
第二个：计算用户在某时间段内考勤记录的总时长和次数
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3GO
4— =============================================
5— Author: 张玉丰
6— Create date: 2008/8/13
7— Description: 用于实现计算用户在某时间段内考勤记录的总时长和次数
8— =============================================
9ALTER PROCEDURE [dbo].[AttendTotal]
10(@username nvarchar(256),
11@sDate datetime,
12@eDate Datetime
13)
14
15AS
16BEGIN
17declare @Aid int
18declare @Count int
19declare @TotalTime int
20declare @ACount int
21—
22—declare @iTry int
23—set @iTry=1
24
25set @TotalTime=0
26 select * into #zyf_temp from Attend
27 where (UserName=@username) and
28 (AttendTime between @sDate and @eDate) and isActive=0
29set @Count=(select count(*) from #zyf_temp)
30set @ACount=@Count
31while (@Count>0)
32begin
33 set @Aid=(select min(Attendid) from #zyf_temp)
34 set @TotalTime=@TotalTime+(select datediff(minute,(select InTime from #zyf_temp where AttendId=@Aid),(select OutTime from #zyf_temp where AttendId=@Aid)))
35 delete from #zyf_temp where AttendId=@Aid
36set @Count=(select count(*) from #zyf_temp)
37end
38drop table #zyf_temp
39—select @TotalTime as ok,@Aid as AID,@iTry as Try
40select @TotalTime as Atime,@ACount as Acount
41END
42
43
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
第三个：输出考勤记录排行榜
版权声明：本文为amboyna原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。