怎么解决这个问题？ [英] how to solve this issue?

问题描述

使用sp计算员工迟到如下

calculating employee lateness using the sp as below

ALTER PROCEDURE [dbo].[spGetEmployeeLateness] --spGetEmployeeLateness '03/01/2014'
@Date as varchar(20)
AS
BEGIN
	select SempAtndSempMstrId,(SempMstrFirstName+' '+isnull(SempMstrMiddleName,'')+' '+
	isnull(SempMstrLastName,'')) as 'EmpName',SdeptDepartmentName,SempAtndTimeIn,SshftInTime,
	
	cast(((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
    - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))/(60*60) as varchar)+':'+	

   cast((((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
   - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))%(60*60))/60 as varchar)+':'+	
	
	cast((((datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),SempAtndTimeIn))
   - (datediff(second,CAST(CONVERT(VARCHAR,@Date,101) AS DATETIME),
	CAST(CONVERT(VARCHAR,@Date,101)+' '+SshftInTime AS DATETIME))))%(60*60))%60 as varchar) as Lateby
		
	
	from SgEmployeeAttendance
	inner join dbo.SgEmployeeMaster on SempMstrId=SempAtndSempMstrId
	inner join SgShiftMaster on SempMstrSshftId=SshftId inner join SgDepartment on SempMstrSdeptId=SdeptId
	inner join SgDesignation on SempMstrSdesgId=SdesgId
	where  cast(convert(varchar(10),SempAtndTimeIn,101)as datetime)=cast(convert(varchar(10),@Date,101) as datetime)
	
	--and  cast(convert(varchar(10),SempAtndTimeIn,101)as datetime)<= DATEADD(day,1,@Date)
	and SempAtndDuty is null and SempMstrId!=1 
	
	group by SempAtndSempMstrId,SempMstrFirstName,SdesgDesignation,SdeptDepartmentName,SempMstrMiddleName,SempMstrLastName,SshftInTime,SempAtndTimeIn
	having min(cast(SempAtndTimeIn as time))>(cast(SshftInTime as time)) order by EmpName
END

但是，如果一名员工在2014年3月1日晚上11:30转移，如果他将在2014年3月2日上午12:05:00来到

，那么如何计算迟到？



上述sp calucate迟到只在日期基础上。



谢谢.. :(

but, if an employee shift on 03/01/2014 at 11:30 pm and if he will
come on 03/02/2014 at 12:05:00 am , then how to calculate lateness?

the above sp calucate lateness only on a date base.

thanks..:(

推荐答案

检查一下...... 两个日期之间的sql时差导致hhmmss [ ^ ]



希望这会对你有所帮助。



干杯

Hi,

Check this...sql time difference between two dates result in hhmmss[^]

Hope this will help you.

Cheers

这篇关于怎么解决这个问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！