sql 考勤中的時間，是否遲到否函數（SQL SERVER 2000）

---得到遲到多少時間或沒有遲到 Geovin Du 塗聚文
declare cursor_select cursor for  select WorkingHoursTime,WorkingHoursUidKey FROM WorkingHoursSet
declare @ntime smalldatetime,@uidkey Uniqueidentifier,@timeint int,@empoyee Uniqueidentifier,@WorkAttendanceUid Uniqueidentifier
--set @uidkey='E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A'
--set @empoyee='B8C15A91-D061-4626-8F7A-79D2F16D3A96'
set @WorkAttendanceUid='06335791-F6A3-4B42-B15E-4F3CC711D442'

open cursor_select
fetch cursor_select into @ntime,@uidkey
while @@fetch_status=0
begin 
      --select @ntime,@uidkey
     if @uidkey='E585A963-58C9-4B8D-A610-9640E8BADFDE'  --膳入
      begin
         --select @ntime,@uidkey
         select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey
	SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))  
	FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
	AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
          select ISNULL(@timeint,0)
       end
    if @uidkey='6E497449-9AEE-4921-8D2D-936C8CE027E0' --膳出
	begin 
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		select ISNULL(@timeint,0)
        end
    if @uidkey='1B823F2E-6EAB-4F3F-80C0-BEC720CE087D' --上班  Geovin Du 塗聚文
	begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
		select ISNULL(@timeint,0)
	end
    if @uidkey='E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A'  --下班  Geovin Du 塗聚文
        begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		select ISNULL(@timeint,0)
	end   
    fetch cursor_select into @ntime,@uidkey
end
close cursor_select
deallocate cursor_select



declare @ntime smalldatetime,@uidkey Uniqueidentifier,@timeint int,@empoyee Uniqueidentifier,@WorkAttendanceUid Uniqueidentifier,@time smalldatetime
set @uidkey='E585A963-58C9-4B8D-A610-9640E8BADFDE'
--set @empoyee='B8C15A91-D061-4626-8F7A-79D2F16D3A96'  Geovin Du 塗聚文
set @WorkAttendanceUid='06335791-F6A3-4B42-B15E-4F3CC711D442'

     if @uidkey='E585A963-58C9-4B8D-A610-9640E8BADFDE'  --膳入
      begin
         --select @ntime,@uidkey
         select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey --規定設定時間
	SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime  
	FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
	AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
          select ISNULL(@timeint,0)
       end
    if @uidkey='6E497449-9AEE-4921-8D2D-936C8CE027E0' --膳出
	begin 
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		select ISNULL(@timeint,0)
        end
    if @uidkey='1B823F2E-6EAB-4F3F-80C0-BEC720CE087D' --上班
	begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
		select ISNULL(@timeint,0)
	end
    if @uidkey='E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A'  --下班
        begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime   
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		select ISNULL(@timeint,0)
	end   
GO


select [dbo].[GetLateTimeInputTwo] (WorkAttendanceUidKey,WorkAttendanceUid),WorkAttendanceDatetime from dbo.WorkAttendanceReport

select [dbo].[GetLateTimeInputTwo] (WorkAttendanceUidKey,WorkAttendanceUid) AS '遲到時間',WorkAttendanceDatetime,WorkingHoursTypeName,EmployeeName from View_WorkAttendanceLateTime



---算是否考勤遲到
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLateTimeInputTwo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetLateTimeInputTwo]
GO
CREATE   function  GetLateTimeInputTwo
(  
   @uidkey Uniqueidentifier,
   @WorkAttendanceUid Uniqueidentifier --参数
)
returns int
as
begin
	declare @ntime smalldatetime,@timeint int,@time smalldatetime
     if @uidkey='E585A963-58C9-4B8D-A610-9640E8BADFDE'  --膳入
      begin
         --select @ntime,@uidkey
         	select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey
		SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
          	--return ISNULL(@timeint,0)
       end
    if @uidkey='6E497449-9AEE-4921-8D2D-936C8CE027E0' --膳出
	begin 
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		--return ISNULL(@timeint,0)
        end
    if @uidkey='1B823F2E-6EAB-4F3F-80C0-BEC720CE087D' --上班
	begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime)) ,@time=WorkAttendanceDatetime  
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,WorkAttendanceDatetime,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime))<0
		--return ISNULL(@timeint,0)
	end
    if @uidkey='E0ABDEC2-4BC9-49CB-85EE-E6EF6D8E183A'  --下班
        begin
		select @ntime=WorkingHoursTime FROM WorkingHoursSet where WorkingHoursUidKey=@uidkey--
		SELECT @timeint=DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime),@time=WorkAttendanceDatetime   
		FROM View_WorkAttendanceLateTime where WorkAttendanceUid=@WorkAttendanceUid and WorkAttendanceIsStatistics=1
		AND DateDiff(minute,cast(convert(char(10),WorkAttendanceDatetime,120)+' '+Convert(varchar(8),@ntime,108) as datetime), WorkAttendanceDatetime)<0
		--return ISNULL(@timeint,0)
	end 
return ISNULL(@timeint,0)
end  
GO