By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,361 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,361 IT Pros & Developers. It's quick & easy.

Calculating Opentime between several records

P: 2
Basically, I am trying to calculate each minute of an employee scheduled time during their work shift. In the table below shows only the times that the shift begin (start_minute), ended (sched_endtime) and times (dtl_start_min, dtl_length, dtl_end_min) the employee went on Break, lunch, etc... What the table does not show is the times that the employee was not on any breaks, lunch etc.. that is called OPENTIME. I am trying to retrieve the OPENTIMES that the employee was available. Below is a sample of the data that I am trying to retrieve. Keep in mind this table contains millions of records.


DATE EMP_ID START_MINUTE SCHED_ENDTIME DTL_EXCPT_DESC DTL_START_MIN DTL_LENGTH DTL_END_MIN
6/19/2008 19261086 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261086 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261086 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261087 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261087 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261087 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261090 10:00:00 19:00:00 Break 12:00:00 0:15:00 12:15:00
6/19/2008 19261090 10:00:00 19:00:00 Lunch 15:00:00 1:00:00 16:00:00
6/19/2008 19261090 10:00:00 19:00:00 Break 17:15:00 0:15:00 17:30:00
6/19/2008 19261091 8:00:00 17:00:00 Break 10:15:00 0:15:00 10:30:00
6/19/2008 19261091 8:00:00 17:00:00 Lunch 11:30:00 1:00:00 12:30:00
6/19/2008 19261091 8:00:00 17:00:00 Break 15:45:00 0:15:00 16:00:00
6/19/2008 19261093 9:00:00 18:00:00 VP 9:00:00 3:30:00 12:30:00
6/19/2008 19261093 9:00:00 18:00:00 Lunch 12:30:00 1:00:00 13:30:00
6/19/2008 19261093 9:00:00 18:00:00 VP 13:30:00 4:30:00 18:00:00


For instance Emp #19261086 clocked in at 10:00:00 (start_min) and went on his first break at 12:00:00 (dtl_start_min) - this break lasted 0:15:00, therefore the 1st OPENTIME is 10:00:00 (start_time) and 2:00:00 hours (LENGTH (HR, MI)). Meaning that the employee worked 2 hours before his first break and the next item shows BREAK from 12:00:00 (dtl_start_min - start_time) and the length of the BREAK 0:15:00. The next record shows OPENTIME 2 from 12:15:00 with a 2:45:00 (LENGTH (2HR, 45MI) OPENTIME). Bascially, I am adding the DTL_START_MIN + DTL_LENGTH = next record START_TIME, but must calculate the previous records DTL_START_MIN + DTL_LENGTH to get the OPENTIME.


Employee 19261086
EVENT START_TIME LENGTH (HR, MI)
Open 10:00:00 2:00:00
Break 12:00:00 0:15:00
Open 12:15:00 2:45:00
Lunch 15:00:00 1:00:00
Open 16:00:00 1:15:00
Break 17:15:00 0:15:00
Open 17:30:00 19:00:00
Jun 27 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.