迟到没

时间：2014-12-01 22:13:33 阅读：350 评论：0 收藏：0 [点我收藏+]

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

迟到没

原文：http://www.cnblogs.com/laopo/p/4136059.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)