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