Min/Max and What Else? - Brain Teaser

[Mark L]

An actual business situation, but a brainteaser (- at least for me) nonetheless:

I’m working on a report that summarizes individuals' hours of access to a building. It’s not used for tracking the hours they worked – just the hours they were present in the building. This is done by tracking the swipes of their badges into the building’s turnstiles (both in and out.) The report must provide a break out by Supervisor, Employee, Date, Time In/Time Out – and then a total of the number of hours in the building for a given date.

There are a couple of challenges built into this problem – they could have some relevance so I’ll lay them out:

· Sometimes the badge reader detects a swipe – but the person gets stuck in the turnstile and must swipe again (not uncommon) to get in; sometimes it takes a few swipes before the person is successful. Each swipe generates a record.

· Some people work late shifts, so they may enter on one day and exit on the next – but their hours in the building still need to tracked.

Below I’ve mocked up a sample data file from the turnstile system and the Employee record (report specific data only) and also done a mock-up of what the report should look like. See the attached spreadsheet.

I'm thinking that the Min/Max options might be useful in finding the earliest and latest times within a break - but my brain gets pretty fuzzy beyond that.

Any input would be very, very helpful.

Thanks,

Mark

Mock_Data_-_Rev_2A.zip

[Søren Dyhr]

Well I have send Comment out and reclaim his granny from the pawnbroker, but in an old template of his - did he operate with a 30 hours day, pretty clever I would say!

Next issue is the swipes not passing in first attempt, here do I think that what Albert Harum Alvarez in an old version of Advisor called the scratchpad model and what these days are called the transaction model might fit - listen to this:

http://podcast.Adatasol.com/media/Filemaker_PodCast_03262006.mp3

--sd

[comment]

First, employees finishing after midnight should not be a problem - just combine the date and time into a timestamp and use that.

The multiple swiping is a BIG problem. It's just plain bad data. What's worse, to determine whether a record is valid you need to refer to other records. I don't see a way out of this, other than looping a script to check each record against the previous/next record of the same employee.

[Mark L]

Seren & comment-

Thanks for your thoughts.

One thing I wanted to add, is that I'm not adverse to processing the data with a script and creating a summary file - and using that file to run the report from.

Does that flesh things out a bit better?

Thanks again - one and all,

Mark

[comment]

Processing the data is no brain teaser - just tedious. Briefly, you need to loop through the swipe records (sorted by employee and timestamp), and do one of the following:

If this record is of the same employee and of the same type (In/Out) as the previous record - delete it.

If this record is of the same employee and of a different type - set the type in the global field gType to this record's type.

If this record is of different employee - set the EmployeeID in the global field gEmployeeID to this record's EmployeeID, and set the type in the global field gType to this record's type.

I am thinking that with multiple swiping, it is the latest swipe that should be kept - if so, you need to loop from the last record backwards, or sort the records by employee, then by timestamp descending.

[Mark L]

comment-

Thanks so much for getting back to me on this. Sorry to allow this topic to go with no response for so long - I've just been overwhelmed with work - and kept forgetting to get back to the forum - Again - mea culpa.

I think you are right - "tedious" might be the best word to describe the situation.

Anyway...With the multiple swiping issue - you are right, the most recent record is the only one I care about. I guess I have to always check to see if the record type has changed or not. BUT this leads into another, more confusing circumstance:

Someone who works a late shift swipes in, late in the evening and then does not swipe out the same day; only to swipe out the next morning (with no swipe in that day.) In my mock report I had to indicate that there was no swipe out for that day - and on the next day, indicate that there was no swipe in. You can check my previous attachment. Any thoughts on that aspect?

Thanks again - and I promise a much quicker response this time.

-Mark

PS - Just reread your idea about combining the date and time to make a time stamp - but I'm still confused about how I'd indicate (see previous attached example in this topic) no recorded swipe in or out for a given day.

Edited April 17, 2006 by Guest

[comment]

I can think of several options, none of them too fast or elegant. You see, the REAL problem here is you are starting with a difficult data structure. A better system, I think, would open a record on swipe in, and close it on swipe out. The way you have it now, it's like deposits and withdrawals in a bank account.

So you could look for employees whose "balance" on a particular midnight is not 0. You can get the balance by setting a global timestamp, and counting related Ins and Outs (separately, the relationships being filtered by gTimestamp > Timestamp).

Another option is to relate In to Out (match In to Out on Employee ID and date). Swipe Ins that don't have a matching out (on the same date), and vice versa, are the orphans you are looking for.

Unfortunately, these are finds on unstored fields, so they won't be quick.

[Charles Delfs]

Keep this in mind, if there are multiple swipes ( I have a health club and this is a simular problem) calc the time between them and if under say 15 minutes it is a false record.

You can set a relationship to itself and filter out its own record with the 'not equals' and sort the records by the timestamp (desending) thus the first recrd the relationship sees is the one right before it.

Now calc the difference in time by the difference between the current record time stamp and the same timestamp in the related record. If under the 15 mins min. then eval that calc to 'bad scan'otherwise it holds the in building time (and also the out of building time)later on a daily script find and delete the bad scans if you want to.

Charles

[Mark L]

Charles and comment-

Thanks again for your valuable input.

To comment's thought - yes, indeed the data is in a bad structure - but that's the reality of business. The system is integrated with an 20 story building, and they're not about to invest tens of thousands, if not hundreds of thousands of dollars to change the hardware (not meaning computers, but door switches, relays, various sensor types too numerous to mention - all which work with this semi-customized package) in order to upgrade the software that controls it. So I just have to deal with it. (Not that I haven't complained like you too.)

Charles - your idea about a self-join and some type of time limit, isn't a bad idea to investigate - as is comment's idea about keeping a tally of some sort. I'd probably keep the time limit to about a minute - but either way - it would probably do the job.

Thank you both,

Mark