Job design/logic question
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
Job design/logic question
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
Marc
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
I don't have premium content (I'm a slacker, my company won't pay) but I think I get your drift.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 ...
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
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.chulett wrote:I haven't done anything like this, but you're looking to target multiple 15 minute time blocks per clock record, yes?...
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
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.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.
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
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:
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.
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
.
..
Last edited by marc_brown98 on Wed Sep 30, 2009 3:12 pm, edited 1 time in total.
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.
viewtopic.php?t=84998
I would do it all in military / 24 hour time, if at all possible.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
You wouldn't happen to know the thread title? I've searched on a range lookup, but found nothing by ken yet.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 ...
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am