Results 1 to 5 of 5

Thread: "Elapsed time" update statement

  1. #1
    Join Date
    Dec 2004
    Posts
    8

    "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!

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    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))

  3. #3
    Join Date
    Dec 2004
    Posts
    8
    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.

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    Okay, I see what you mean. What database are you using?

  5. #5
    Join Date
    Dec 2004
    Posts
    8
    i'm using DB2 UDB V.8

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •