迟到没

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

转载于:https://www.cnblogs.com/laopo/p/4136059.html