hi, guys I need help finding the best formula to calculate employee punched time
I have tried almost different way but it does not always work for instance
I have a table with 3 Columns with Multiple Data Rows
[EmpId as integer, Punched_In_Out as datetime, Status as varchar(10)]
DOE001, 2014-05-23 9:56:11, IN
DOE001, 2014-05-23 18:07:41, OUT
Worked Hours = (18:07-9:56) result = 8:51 but in reality the result should be 8:03
8 Hours and 3 Minutes
The above formula works for greats but not every time like the case presented above
I have used the DATEDIFF(min,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT))
but this rounds it up to 9 hours
also tried the MOD like this
DATEDIFF(min,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT)) % 60
different result
Does any one know of the best formula to calculate punched time?
Please help this is driving me nuts
TIA
Khan Abrar AhmedPosted May 25, 2014, 3:01 AM
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