14,599,202 members
Rate this:
See more: , +
I'm Trying to calculate the total worked hours by employee:
My table "Log_Attendance" like this

```EnrollNumber       InOutMode       DateAttendance       TimesInOut
27                   0              07-09-2015           07-09-2015 08:00:00
27                   2              07-09-2015           07-09-2015 10:00:00
27                   3              07-09-2015           07-09-2015 11:00:00
27                   1              07-09-2015           07-09-2015 12:00:00
27                   0              07-09-2015           07-09-2015 13:00:00
27                   1              07-09-2015           07-09-2015 17:00:00```

`InOutMode-->  0:CheckIn     1: CheckOut         2:BreakIn        3:BreakOut`

I need to calculate only the worked hours!
example:
```EnrollNumber       TotalWorkedHours
27                       7    (=(10-8)+(12-11)+(17-13))```

Posted

Rate this:

## Solution 1

If I understand correctly, you need to fetch a row for in and for out and to calculate the difference?

If so, perhaps something like:
```SELECT DATEDIFF(hour, out. TimesInOut, in.TimesInOut)
FROM Log_Attendance in,
Log_Attendance out
WHERE in.enrollnumber = out.enrollnumber
AND   in.inoutmode = 0
AND   out.inoutmode = 1
AND   in.DateAttendance = out.DateAttendance
...```

Of course that's not the whole logic but it'll jopefully get you started...
Leila Toumi 10-Jul-15 7:39am

Wendelius 10-Jul-15 7:54am

As said, the conditions are not complete. You need to add possibly missing logic for example what about inoutmodes 2 and 3.

What the example should do is to give you few of the rows and idea how the query could be built.