Formula to calculate employee punched time hours

Question

I have a table with 3 columns with multiple data row
EmpId , PUNCHED_IN_OUT Status
DOE001, 2014-05-23 9:56:01
DOE001, 2014-05-23 18:08:45
I've tried datediff(min,MIN(punched_in_out),MAX(punched_in_out))
the above rounds to 9 hours
also this datediff(min,MIN(punched_in_out),MAX(punched_in_out)) % 60
different result

and this 18:07-9:56 result = 8:51 when the actual result should be 8:03

Please note that the subtracting the Last punched time minus the first punched time

does not work every time

Please help

Thanks

Jose Saiz · Answer

Great it works like a charm it never crossed my mind to use the seconds mod(%) to 3600 instead of the mod(%) to 60. Thanks Again finally I can get the calculated worked hours right. Thank you very much

Khan Abrar Ahmed · Answer

Hi please try this script. DECLARE @table as TABLE ( EmpID varchar(64)PUNCHED_IN_OUT datetime) INSERT INTO @table VALUES ('DOE001''2014-05-23 10:00:11')('DOE001''2014-05-23 11:00:11') SELECT CONVERT(nvarchar(2)(datediff(ssMIN(PUNCHED_IN_OUT)MAX(PUNCHED_IN_OUT))/3600)) + ':' + CONVERT(nvarchar(2)((datediff(ssMIN(PUNCHED_IN_OUT)MAX(PUNCHED_IN_OUT))%3600)/60)) + ':' + CONVERT(nvarchar(2)(datediff(ssMIN(PUNCHED_IN_OUT)MAX(PUNCHED_IN_OUT))%60)) FROM @table