"Elapsed time" update statement

Hello!

I have a table with data structure as follows:

Code:

---

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!

This query (written in SQL Server T-SQL) will return the record with the number of seconds elapsed between the two dates.

Select *, DateDiff(ss, Cast(ENTRYDATE + ENTRYTIME as datetime), Cast(SUP_DATE + SUP_TIME as datetime))
From YourTable


Here it is as an update query assuming that a field called ElapsedTime exists:

Update YourTable
Set ElapsedTime = DateDiff(ss, Cast(ENTRYDATE + ENTRYTIME as datetime), Cast(SUP_DATE + SUP_TIME as datetime))

That works perfect for a single row, but it's a little more complicated than that:

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.

Okay, I see what you mean. What database are you using?

i'm using DB2 UDB V.8