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

?

1 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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

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