最近写的三个关于考勤的存储过程

前一阵做一个考勤记录的东东，自己写了三个存储过程，记录如下：

第一个：记录考勤

set ANSI_NULLS ON
 set QUOTED_IDENTIFIER ON
 GO
 ALTER PROCEDURE [dbo].[DS_Attend]
     (@username nvarchar(256), 
     @action varchar(10))
 AS
 /**//*功能： 本存储过程实现成员的考勤的记录功能，及考勤时间计算功能。 */
 /**//*作者：张玉丰 */
/**//*日期：2007-8-15 */
    
if @action='in'
begin
    insert into Attend(UserName,InTime) values(@UserName,getdate())
    update UserInfo set UIsInCenter=1 where UserName=@username

end

if @action='out'
begin
    declare @startTime datetime --本次签入时间；
    declare @oldTime float  --成员累计时长；
   set @oldTime=(select UinCenterTime from  UserInfo where UserName=@username )
    set @startTime=(select InTime from Attend where UserName=@username and IsActive=1)    
    update UserInfo  set UinCenterTime=@oldTime+datediff(minute,@startTime,getdate())
         where 
        UserName=@username 
    update Attend set OutTime=getdate(),IsActive=0 where UserName=@username and IsActive=1
    
   
    
    update UserInfo set UIsInCenter=0 where UserName=@username

end

 

第二个：计算用户在某时间段内考勤记录的总时长和次数

set ANSI_NULLS ON
 set QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        张玉丰
 -- Create date: 2008/8/13
 -- Description:    用于实现计算用户在某时间段内考勤记录的总时长和次数
 -- =============================================
 ALTER PROCEDURE [dbo].[AttendTotal]
(@username nvarchar(256),
@sDate datetime,
@eDate Datetime
)

AS
BEGIN
declare @Aid int
declare @Count int
declare @TotalTime int
declare @ACount int
--
--declare @iTry int
--set @iTry=1

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
select @TotalTime as Atime,@ACount as Acount
END

 第三个：输出考勤记录排行榜

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