I think my query will satisfy your requirement... As per my understanding you want to get the total out time in a day per employee.. If this is the one, you go ahead with below query:
DECLARE @Emp TABLE(EmpId INT, EmpName VARCHAR(10), InTime DATETIME, OutTime DATETIME, dstatus VARCHaR(5), whours INT)
insert @Emp
SELECT 2500, 'Arun', '2014-01-01 08:30', '2014-01-01 09:30', NULL , NULL union all
SELECT 2500, 'Arun', '2014-01-01 09:40', '2014-01-01 11:30', NULL , NULL union all
SELECT 2500, 'Arun', '2014-01-01 12:30', '2014-01-01 12:45', NULL , NULL union all
SELECT 2501, 'Kiran', '2014-01-01 14:10:00.000', '2014-01-01 18:30', NULL, NULL union all
SELECT 2502, 'Arsh', '2014-01-01 11:12:00.000', '2014-01-01 16:30', NULL, NULL
--i want to find out the total working hours of each employee
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay
,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total work_hour(s)]
,convert(time, max(OutTime)-MIN(InTime)) as Emp_Intime
,CAST( max(OutTime)-MIN(InTime) - CAST( RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS DATETIME) AS TIME) OutTimeHH:MI:SS
FROM @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator