Click here to Skip to main content
14,599,202 members
Rate this:
Please Sign up or sign in to vote.
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!
EnrollNumber       TotalWorkedHours
27                       7    (=(10-8)+(12-11)+(17-13))

Any Solution please

1 solution

Rate this:
Please Sign up or sign in to vote.

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
thanks for your reply but it gives me wrong value !!
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100