sql function

[Thanusree Duth]

My sql function is ALTER FUNCTION[dbo].[FN_ATTENDANCE_REPORT_DATES] ( @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, ISNULL(CONVERT(VARCHAR,DR_TimeIn,103),'Not checked')AS DATEOFWORK, SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)AS SHIFTTIME, ISNULL(SUBSTRING(CONVERT(NVARCHAR,MIN(DR_TimeIn), 22), 10, 11),'Not checked') AS FIRSTTIMEIN, ISNULL(SUBSTRING(CONVERT(NVARCHAR,MAX(DR_TimeOut), 22), 10, 11),'Not checked') AS LASTTIMEOUT, 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')AS TOTALHOURS 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_DeptID=SG_Emp_Department.ED_DeptID WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101) BETWEEN @FROMDATE AND @TODATE GROUP BY DR_EmployeeID,EM_FirstName+EM_MiddleName+EM_LastName,ED_Department,ES_TimeIn,ES_TimeOut,DR_TimeIn,DR_TimeOut ) I got output as FIRSTTIMEIN LASTTIMEOUT 2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 9:40:45 PM 2:30:36 2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 10:20:32 PM 12:15:27 AM 1:54:55 2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 1:24:23 AM 1:0:58 2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 1:32:43 AM 3:45:54 AM 2:13:11 But I have to get output as 2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 12:15:27 AM 4:26:26 2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 3:45:54 AM 3:14:09 pls help

[Simon_Whale]

could do with knowing the following 1. what version of SQL? 2. Sample Data 3. Tables (DDL)

As barmey as a sack of badgers

[J4amieC]

This is the 3rd question on the same topic. You should stick to 1 thread for 1 topic. Ive also pointed out the forum guidlines which ask you to use descriptive subject lines . "sql function" does not qualify as descriptive in a sql forum. The forum guidlines should also include the request to put code within formatted code blocks for ease of reading (if it doesnt it should). Furthermore, you have not given any response to the answers youve been given below, so we have no idea if were even on the right track. Lastly, Ive already answered this question below.