Job design/logic question

marc_brown98 · Post by **marc_brown98** » Wed Sep 09, 2009 12:55 pm

I was looking to get some feedback or suggestions as to how to structure the logic or design for ETL. I have a time clock data coming from a time keeping system. Basically it has 'clock-in' and 'clock-out' date and time. The requirement I have is to provide a Headcount(how many employees on the clock) during a given 15 minute time period. So, given that I have only a start and end time, I need to associate or caclulate a 'dwell' time down to the minute or at least 15 minute block (1/4 hour). Any thoughts or suggestions, has anybody done something like this? THanks in advance.

Marc

DSguru2B · Post by **DSguru2B** » Wed Sep 09, 2009 1:01 pm

So you need a head count of all employees that have clocked in but not clocked out yet (in the past 15 mintues) or regardless?
Either way, you have the clock in and out times, let yout job calculate the time that was 15 mintues ago by going back 900 seconds from the current timestamp and then extract your data using a between clause with your calculated start and end times.
This might get tricky if there are day cross overs as you have to take that into account while calculating your begin and end dates.

marc_brown98 · Post by **marc_brown98** » Thu Sep 10, 2009 2:21 pm

DSguru2B wrote:So you need a head count of all employees that have clocked in but not clocked out yet (in the past 15 mintues) or regardless?
Either way, you have the clock in and out times, let yout job calculate ...

I don't have premium content (I'm a slacker, my company won't pay) but I think I get your drift.

chulett · Post by **chulett** » Thu Sep 10, 2009 2:47 pm

I haven't done anything like this, but you're looking to target multiple 15 minute time blocks per clock record, yes?

marc_brown98 · Post by **marc_brown98** » Fri Sep 11, 2009 10:04 am

chulett wrote:I haven't done anything like this, but you're looking to target multiple 15 minute time blocks per clock record, yes? ...

Yes, my initial plan is to do down to the minute (I know, it's a long story), but my time dimension at the end of this processing will allow it to be rolled up by 15 minute increments.

marc_brown98 · Post by **marc_brown98** » Mon Sep 14, 2009 8:54 am

DSguru2B wrote:So you need a head count of all employees that have clocked in but not clocked out yet (in the past 15 mintues) or regardless?
Either way, you have the clock in and out times, let yout job calculate the time that was 15 mintues ago by going back 900 seconds from the current timestamp and then extract your data using a between clause with your calculated start and end times.
This might get tricky if there are day cross overs as you have to take that into account while calculating your begin and end dates.

Yes, we do have days that cross over, however, I do have an 'operational day' that is stored too. So, just a little logic is needed to span the correct day. So, yes, just calculating headcount I think is more straight forward than I thought. One wrinkle in it though is that of course employees forget to swipe in/out and there are management adjustments that are made. So, it gets a little more interesting. But, thanks for the advice, it gives me some direction to head and see how it works.

marc_brown98 · Post by **marc_brown98** » Wed Sep 30, 2009 12:59 pm

So, I did get the business to agree to 15 minute increments rather than by the minute. What I'm basically looking at now is the following:

Code: Select all

Employee #    Clock In   Clock Out
1                    8:15 AM    3:30PM  
....

Time Increment ID's    Start Hour/Minute            End Hour/minute
1                                 08:00                            08:14
2                                 08:15                            08:29
3                                 08:30                            08:44
4                                 08:45                            08:59
...


What needs to be would be something like
Employee #    Time Increment ID    Headcount
1                    2                                1
1                    3                                1
1                    4                                1
.
..

I'm trying to avoid having a substantial amount of 'buckets' for the time increments, but I'm open to efficient ways to accumulate these values. Any help would be much appreciated.

chulett · Post by **chulett** » Wed Sep 30, 2009 1:24 pm

If I were you, I would seriously consider this technique posted by Ken Bland back in the day for using hashed files to do range lookups. I've implemented it in the past and it is sweet once it all clicks for you and you grok what is going on, that and the actual lookup itself is fast as well. Seems like it would work well with your Time Increment ID process.

viewtopic.php?t=84998

I would do it all in military / 24 hour time, if at all possible.

marc_brown98 · Post by **marc_brown98** » Wed Sep 30, 2009 2:38 pm

chulett wrote:If I were you, I would seriously consider this technique posted by Ken Bland back in the day for using hashed files to do range lookups. I've implemented it in the past and it is sweet once it all cli ...

You wouldn't happen to know the thread title? I've searched on a range lookup, but found nothing by ken yet.

chulett · Post by **chulett** » Wed Sep 30, 2009 4:01 pm

I posted a direct link to the thread, but you can search for "KeyRollup" with an author of "kcbland" and you should only get one hit.

marc_brown98 · Post by **marc_brown98** » Thu Oct 01, 2009 7:36 am

Got it. Just waiting on my renewal for the premium membership..tick tock..