Calculating Opentime between several records.Using SQL Server 2000.

[RubyRed]

Using SQL Server 2000. 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

[Delerna]

Your comment to 'keep in mind that the table contains millions of records' suggests to me you know the subquery method for getting the next record for a particular entity. This is the only way I know of to do such a task.

Can you insert the 'Open' records into the table. If so you could run the above on a regular basis to insert them. That way you could arrange to only work with the records that have been added since the last time the insert routine ran. If you know what I mean