Question How to get the Total spent hours of the employee

[mounika]

Hi Team,

I am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.


I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem. Can you please help me by giving any suggestion? Thank in advance , please help me ..

SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #01/20/2014# and #01/24/2014#
GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;

 

[MarkK]

Your data seems to be in conflict with itself, because the first two entries for Pradeep K. in Master are . . .

477	Pradeep Keesari	1/20/14	Entry	20:24:32
477	Pradeep Keesari	1/20/14	[COLOR="Red"]Entry	23:08:39[/COLOR]

. . . but in Swipe, that same data is . . .

477	Pradeep Keesari	1/20/14		20:24:32	[COLOR="Red"]23:08:39[/COLOR]	2:44

. . . so in Swipe, 23:08:39 is Exit. In Master, it is Entry, so one of them is simply wrong.

And, there is another problem with the data in Master . . .

477	Pradeep Keesari	1/21/14	Exit	22:28:15
477	Pradeep Keesari	1/21/14	Exit	22:28:17
477	Pradeep Keesari	1/21/14	Exit	22:28:19

. . . three exits in 4 seconds. And not one of those appears in Swipe.

Other thoughts: I would want date/time in the same field. Date/time is really one dimension, like feet/inches and dollars/cents. If you separate them from each other, then they get harder to work with.

Another little trick, if you consider entry as -1 and exit as +1, then you have a new field called Direction, essentially a numeric version of what you have, but with the number you can do very handy math . . .

SELECT Sum(Direction * DateTime) As TotalTime
FROM YourPunchInOutTable
WHERE SwipeDate > x and SwipeDate < y
AND EmployeeID = z

This has the net effect of subtracting all the Entries from all the Exits, and leaves you with the exact total hours for that person for that date period.

Hope this helps,

 

[mounika]

Hi Lagbolt,

Appreciate your help regarding the solution.

I guess, your solution holds good for a period of time.
Example: To generate a report on weekly time an employee has spent at office.

In our case, we are looking to generate a report which gives daily time spent by each employee at office.
We are looking at something like this:
SELECT employee_id, week_date, Sum(Direction * DateTime) As TotalTime FROM YourPunchInOutTable WHERE SwipeDate between week_startdate and Week_enddate
group by employee_id, week_date


The above query looks good for those employees who work during day shifts.
But it doesn't seem to address the case of employees who work during night shifts.

Example:
27/Jan
entry 6.00 Pm i.e. 1800 * -1
exit 9.00 Pm i.e. 2100 * +1
entry 10.00 Pm i.e. 2200 * -1

Net Hours : -1900 hours
28/Jan
exit 3.00 AM = 0300 * +1

entry 6.00 pm = 1800 * -1
exit 9.00 Pm = 2100 * +1
entry 10.00 PM = 2200 * -1

Net Hours = - 1600

so on so fourth....
Please advise for the above situation.

Thanking You.
Mounika

 

[MarkK]

You have to have date and time in the same field. This is not a way to find hours per day, since some of your shifts--if you cut off the day at midnight--will be in-progress. This method requires even ins and outs, but every accurate solution to summing time will have that attribute.

In this it is easy also to test. Sum your Direction(s) . . .

SELECT Sum(Direction) As Testing
FROM . . .

. . . and if it is zero, then you have equal entries and exits. If the result is negative, your first item was an exit, not an entry. These are some pretty cool and easy tests to perform on a complex problem.

And this is very simple and effective math . . .

Private Sub Test1267419764()
    Dim d As Date
    
    d = _
    -1 * #1/27/2014 6:00:00 PM# _
    + 1 * #1/27/2014 9:00:00 PM# _
    - 1 * #1/27/2014 10:00:00 PM# _
    + 1 * #1/28/2014 3:00:00 AM# _
    - 1 * #1/28/2014 6:00:00 PM# _
    + 1 * #1/28/2014 9:00:00 PM#
    
    Debug.Print CSng(d * 24) & " hours total"
    
End Sub

. . . and even simpler if you do it using SQL on table data.

I don't insist on this idea, but you have a tough problem to solve, and it seems worthwhile to mention that this method offers some simplicities. Maybe other complexities, but it is, obviously, your choice