Working out Different between several times

[Guest]

OK, this one has been rattling my brains for a while now and i'm not sure how
to work this out.

Lets say i have a table with the following data

StaffRef DateTime Type
1 30/03/2005 08:02:54 IN
1 30/03/2005 10:03:04 OUT
1 30/03/2005 10:13:05 IN
1 30/03/2005 12:03:08 OUT
1 30/03/2005 12:34:28 IN
1 30/03/2005 17:00:07 OUT
2 30/03/2005 08:03:05 IN
2 30/03/2005 10:03:05 OUT
2 30/03/2005 10:12:08 IN
2 30/03/2005 12:04:12 OUT
2 30/03/2005 12:24:12 IN
2 30/03/2005 17:00:56 OUT

This says for 1 day (altho there will be many days) 2 members of staff have
signed in and out repeatedly (lunch, breaks, etc.). Basically i'm looking
for a way to find out how much time was taken as breaks/lunch, etc. for each
member of staff for that day. So we have starting time and ending time,
which i can find out myself using min and max. But How can i work out the
time difference between the OUTs and the INs before the end of the day, to
work out total time taken?

Any help on this would be appreciated.

Many thanks.

Phill

 

[Nikos Yannacopoulos]

Phill,

I doubt this can be done in a query. I would use two nested loops on
recordsets in VBA code, to loop through StaffRef (outer loop), and for
each one loop through the In and Out times (inner loop) and tally the
[Out]-[In] times.

Anybody got a better idea?

Nikos

 

[Michel Walsh]

Hi,


*ASSUMING* there is one and only one OUT for each IN, it is just a matter
to group by StaffID, and Type, summing the Date_Time. Then, a second query
could make the difference of the IN from the OUT sums. We can do it in just
one query too, using an iif:




SELECT StaffRef, CDbl(SUM(Date_Time * iif(type='OUT', -1, +1)))
FROM myTable
GROUP BY StaffRef



You get a result in portion of day (0.5 = half a day = 12 hours; 0.25 = 6
hours, etc. )


You may add an additional GROUP:

GROUP BY StaffRef, DateValue(Date_Time)



If there is not a prefect match between IN and OUT ( a missing OUT or a
missing IN), the result moves out of scope, evidently.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,

You can check there is one OUT for each IN, and in the appropriate
order, with a running sum.


SELECT a.StaffRef, a.DateTime, SUM(iif(b.type='out', -1, 1)) as Running
FROM myTable As a INNER JOIN myTable As b
ON a.StaffRef=b.StaffRef AND a.DateTime >= b.DateTime
GROUP BY a.StaffRef, a.DateTime


Any Running that is neither 0, neither 1 designates a problem:


SELECT c.StaffRef
FROM (
SELECT a.StaffRef, a.DateTime, SUM(iif(b.type='out', -1, 1)) as Running
FROM myTable As a INNER JOIN myTable As b
ON a.StaffRef=b.StaffRef AND a.DateTime >= b.DateTime
GROUP BY a.StaffRef, a.DateTime
) As c
GROUP BY c.StaffRef
HAVING MIN(c.Running)<0 OR MAX(c.Running)>1


where the inner query is the previous one. You can use a saved query,
instead of the actual syntax


That query checks not only the number of IN and the number of OUT, but that
they appear in the right order. The sequence: IN IN OUT OUT, or the
sequence: OUT IN OUT IN will both be detected as illegal, while a simple
count would not detect the problem.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for this, but it doesnt quite work how i had hoped. Basically the
end result appears to be the total numbers of hours and minutes between the
IN and the next OUT, rather than between the OUT and the IN. To explain a
little further, the answer this gives me for say staff member 1 is 08:45,
which is the total number for that day barring the breaks, etc. I need the
total for the breaks. I hope you understand what i mean by this as it is a
little confusing.

Thanks for getting me this far tho' whether you can assist further or not.

Phill

 

[Guest]

I think you are just about where you want to be, if you review your original
problem post. What you have now is the total number of hours the individual
was in the area, and you have a way to verify that you have the expected
login sequence (IN then OUT, then IN and then OUT again.) Leaving with an
OUT at the end of the calendar day. You should be able to take the Max of an
individual for a given day, minus the MIN for that same day and individual
and use that information to compare to the total amount of IN time spent.
I.e. individual signs in at 8am and last out is at 8pm, then total time
between is 12 hours. If they were only in for 08:45, then 12 hours minus
8:45 hours, means that the individual was out for 3:15.

Not exactly sure the full coding for it, but you are almost to the end and
maybe someone else can help with the specific code if you haven't figured it
out on your own.

 

[Michel Walsh]

Hi,


Indeed, the first query can look like:


SELECT StaffRef, DateValue(Date_time),
CDbl(SUM(Date_Time * iif(type='OUT', -1, +1))) As BusyTime,
Max(Date_time) - Min(Date_time) - BusyTime as TimeOff

FROM myTable
GROUP BY StaffRef, DateValue(Date_time)



where the SUM is the busy time, and MAX-MIN is the total time. I also
grouped by date, here, to avoid the inclusion of the time interval between
leaving in the afternoon and arriving next day, in the morning.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Woo hoo, I would like to thank all for contributing that information, it
appears to be working a treat now.

Many thanks.