--For your Sample data
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, DATEDIFF( MI, InTime, OutTime)/60, DATEDIFF( MI, InTime, OutTime)%60
FROM @Emp
--Query for calculating total hours
SELECT EmpId , EmpName,CAST(InTime AS DATE) WorkingDay, SUM( DATEDIFF( MI, InTime, OutTime)/60) Hrs
,SUM( DATEDIFF( MI, InTime, OutTime))%60 Mins
,RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime)/60)), 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total_hour(s)]
FROM @Emp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
NOTE: In the query Just replace @Emp with your TableName
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