迟到没

---得到遲到多少時間或沒有遲到 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