sql function to calculate work hours of an employee

[Thanusree Duth]

Hii.. I m in a great confusion.:confused:Can u help me?I want to calculate total work hours of a day using sql function.I m having three tables. SG_Emp_Master,SG_Daily_Register,SG_Emp_Department.Employee ID,From date,ToDate are the parameters passed..Here is my code..But i could'nt get correct output.pls help me. CREATE FUNCTION[dbo].[FN_TIME_ATTNDNC_REPORTS] ( @EMPID varchar(50) ,@FROMDATE datetime ,@TODATE datetime )RETURNS TABLE AS RETURN( SELECT DR_EmployeeID as EMPID,EM_FirstName+EM_MiddleName+EM_LastName as EMPNAME,ED_Department as DEPARTMENT, SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)as SHIFTTIME, CASE WHEN ((CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))AND(SUBSTRING(CONVERT(VARCHAR,DR_TimeIn,9),14,15)>'11:59:59:000PM')) THEN ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 12:01:00:000AM','Not checked') ELSE ISNULL(CONVERT(VARCHAR,DR_TimeIn,109),'Not checked') END AS TIMEIN, CASE WHEN(CONVERT(VARCHAR,DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101)) THEN ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 11:59:59:000PM','Not checked') WHEN (CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101)) THEN ISNULL(CONVERT(VARCHAR,DR_TimeOut,109),'Not checked') END AS TIMEOUT, CASE WHEN(CONVERT(VARCHAR, DR_TimeIn,101)=CONVERT(VARCHAR, DR_TimeOut,101)) THEN ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600) +':'+ CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60) +':'+ CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0:0:0') WHEN(CONVERT(VARCHAR, DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101)) THEN ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR,DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))/3600) +':'+ CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%3600/60) +':'+ CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%60)),'0:0:0') ELSE 'NOT CHECKED' END AS HOURS FROM SG_Daily_Register INNER JOIN SG_Emp_Shift on SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID INNER JOIN SG_Emp_Master on SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID INNER JOIN SG_Emp_Department on SG_Emp_Master.EM_Dep

[thatraja]

Mate, please format the code because there are many smileys in your sql query.

thatraja

---

Tips/Tricks|Brainbench certifications|Facebook|Twitter