stored procedure
-
SELECT DISTINCT DATEOFWORKS,EMPNAME,DEPARTMENT,SHIFTTIME,FIRSTTIMEIN,LASTTIMEOUT,WORKINGHOURS,BREAKDURATION from [dbo].[fn_emp_Workdetails]('2833409','9/25/2010','9/26/2010')INNER JOIN [dbo].[fn_FirstTimeIn_LasttimeOut] ('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn_FirstTimeIn_LasttimeOut].EMPLID INNER JOIN [dbo].[fn__Emp_Working_Hrs]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID= [dbo].[fn__Emp_Working_Hrs].EMPLOYEE_ID INNER JOIN [dbo].[fn__Break_Duration]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn__Break_Duration].EMPLOY_ID In this stored procedure,the output is 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:21:-24 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:0:0 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:21:-24 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:21:-24 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:21:-24 The same row appear several times.Please help me to select one row only one times.By using 'distinct' it wouldnt become correct..pls help
-
SELECT DISTINCT DATEOFWORKS,EMPNAME,DEPARTMENT,SHIFTTIME,FIRSTTIMEIN,LASTTIMEOUT,WORKINGHOURS,BREAKDURATION from [dbo].[fn_emp_Workdetails]('2833409','9/25/2010','9/26/2010')INNER JOIN [dbo].[fn_FirstTimeIn_LasttimeOut] ('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn_FirstTimeIn_LasttimeOut].EMPLID INNER JOIN [dbo].[fn__Emp_Working_Hrs]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID= [dbo].[fn__Emp_Working_Hrs].EMPLOYEE_ID INNER JOIN [dbo].[fn__Break_Duration]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn__Break_Duration].EMPLOY_ID In this stored procedure,the output is 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:21:-24 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:0:0 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:21:-24 25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:0:0 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:21:-24 26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:21:-24 The same row appear several times.Please help me to select one row only one times.By using 'distinct' it wouldnt become correct..pls help
Use dateparts to remove the time component of the fields with time values. You can also do a double convert > varchar using a formater convert(varchar(20),date,103), then convert the result back to datetime format and use that in the report
Never underestimate the power of human stupidity RAH