"Elapsed time" update statement

[jcale4]

Hello!

I have a table with data structure as follows:

ORIG_OP ENTRYDATE ENTRYTIME  SUP_ID SUP_DATE   SUP_TIME
------- --------- ---------- ------ --------   ---------
L5T     05/10/2005 11:26:35  L5T    05/10/2005 11:26:35
L5T     05/11/2005 07:54:01  L5T    05/11/2005 07:54:01
D4B     06/03/2005 10:15:35  D4B    06/03/2005 10:15:35
D4B     06/03/2005 10:15:35  F90    06/06/2005 13:36:53
VT1     04/21/2005 10:50:39  VT1    04/21/2005 10:50:39
VT1     04/21/2005 10:50:39  F90    04/21/2005 15:05:04
VT1     04/21/2005 10:50:39  VT1    04/25/2005 13:43:48
D4B     04/25/2005 08:51:20  D4B    04/25/2005 08:51:20
D4B     04/25/2005 08:51:20  F90    04/27/2005 08:21:15
D4B     04/25/2005 08:51:20  F90    04/27/2005 08:21:15
D4B     04/25/2005 08:51:20  D4B    05/13/2005 06:39:38
D4B     04/26/2005 09:06:53  D4B    04/26/2005 09:06:53
D4B     04/26/2005 09:06:53  F90    04/27/2005 08:21:54
D4B     04/26/2005 09:06:53  F90    04/27/2005 08:21:54
D4B     04/26/2005 09:30:23  D4B    04/26/2005 09:30:23
D4B     04/26/2005 09:30:23  F90    04/27/2005 08:22:12
D4B     04/26/2005 09:30:23  F90    04/27/2005 08:22:12

You get the point. .. obviously the orig_op field holds the original opid of the first person to hold the "entry". The sup_id is the person who adds to the original "entry" along with the sup_date and sup_time. I need to be able to calculate elapsed time for each record. So, i need to take the first record in the set ('set' is defined by the orig_op, entrydate, and entrytime combination) and post an elapsed time in a new field, then take the next record and calculate the time between the previous records sup_date & sup_time and the current record, then stamp the elapsed time on that record, etc. This seems like some pretty tricky SQL. Anyone have any ideas?

Thank you so much!

 

[rac2]

Would it be correct to say that you wish to calculate the elapsed time between the end of one record and the beginning of the next?

I think that calulations involving data from different rows usually requires a procedure and cannot be done in a SQL query. Especially when the number of records in the set is variable. The procedure could use a cursor to step through the records, do the calculation, and store the elapsed time.

 

[jcale4]

Yes, i believe that would be correct, however i'm not very good with PL/SQL. Basically, the records are grouped by entrydate, entrytime, and orig_op. This is how I determine that it is all part of an original record in another table. I need to group those and then for each record inside that grouping, take the sup_date + sup_time of the record before it and stamp the difference. This table data is inserted via LOAD statement each night, so its not as easy as calcualating during insertion of each record.. the calcualtion/procedure would be performed each night after the load.

thank you!