Formula to calculate employee punched time hours

Jose Saiz

12y1.1k.NET

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jose Saiz12y ago
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 Ahmed12y ago
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(ss,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT))/3600)) + ':' +
CONVERT(nvarchar(2),((datediff(ss,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT))%3600)/60)) + ':' +
CONVERT(nvarchar(2),(datediff(ss,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT))%60))

FROM @table