Calculate time difference between rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Calculate time difference between rows

Hybrid View

  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Calculate time difference between rows

    Hi,

    I have record stored as shown, (Please find attached file if format not clear)

    SHIPMENT_ID MESSAGE_TYPE CREATED_DTTM
    9336 5 9/29/2006 9:16:14 AM
    9336 10 9/29/2006 10:33:16 AM
    9379 5 10/5/2006 11:43:33 AM
    9379 10 10/6/2006 7:19:38 AM
    10128 5 10/3/2006 8:21:25 AM
    10128 10 10/4/2006 10:20:03 AM

    and using SQL would need to calculate time difference between message_type 5 and 10 for each Shipment_id.

    (9/29/2006 9:16:14 AM) - (9/29/2006 10:33:16 AM)

    (10/5/2006 11:43:33 AM) - (10/6/2006 7:19:38 AM)

    (10/3/2006 8:21:25 AM) - (10/4/2006 10:20:03 AM)

    Expecting results in format after calculating time difference from above for each shipment_id,

    SHIPMENT_ID TIME_DIFFERENCE

    9336
    9379
    10128

    Thanks,
    Siva
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ...and your question is?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    Thanks for asking.

    I need to know SQL statement to achieve the expected result.

    -Siva

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    I'm not sure about your example either, but if you want to look at something from another row in a given query subset you can use the lead/lag analytic tools. Here are two links that describes the lead/lag functions as they relate to the partition by clause.

    hXXp://www.stanford.edu/dept/itss/do...s.htm#i1007032
    hXXp://www.oracle-base.com/articles/...cFunctions.php
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    If you are an old school person you can also join your table with itself by listing it twice in your FROM clause.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Apr 2008
    Posts
    3
    Start time and end time is stored seperately in two different records and I would need to calculate time difference. Shipment_Id values identifies relation between records.

    QUestion is,

    I need to know SQL statement to generate this result. Can you give me a sample SQL.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    Quote Originally Posted by Sivak
    Start time and end time is stored seperately in two different records and I would need to calculate time difference. Shipment_Id values identifies relation between records.

    QUestion is,

    I need to know SQL statement to generate this result. Can you give me a sample SQL.
    Write the query without the lead/lag, is the row that you are looking for immediately preceding or following the row that you are comparing to? Is the offset always the same? According to the links you need to know the row offset.
    this space intentionally left blank

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Okay... I'm not doing your job but I'll gave you a jump start...
    Code:
    select   a.shipment_id  as SHIPMENT,
             a.created_dttm as TYPE_5,
             b.created_dttm as TYPE_10
    from     your_table a,
             your_table b
    where    a.shipment_id  = b.shipment_id
         and a.message_type = 5
         and b.message_type = 10
    order by a.shipment_id
    /
    ...time for you to show your skills.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    once i came with a thread asking suggestion to add new features in 11G..,

    subtraction of timestamp that should result in timestamp could be added as a new feature in 11G.,

Posting Permissions

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



Click Here to Expand Forum to Full Width