Thread: Calculate time difference between rows

1. Junior Member
Join Date
Apr 2008
Posts
3

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

3. Junior Member
Join Date
Apr 2008
Posts
3

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

-Siva

4. 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

5. If you are an old school person you can also join your table with itself by listing it twice in your FROM clause.

6. Junior Member
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. 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.

8. 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.

9. Member
Join Date
Jan 2007
Posts
231

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
•