# Calculate time difference between rows

• 04-29-2008, 06:29 PM
Sivak
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
• 04-30-2008, 05:42 AM
PAVB
• 04-30-2008, 10:13 AM
Sivak

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

-Siva
• 04-30-2008, 10:26 AM
gandolf989
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
• 04-30-2008, 10:38 AM
PAVB
If you are an old school person you can also join your table with itself by listing it twice in your FROM clause.
• 04-30-2008, 11:07 AM
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.
• 04-30-2008, 12:12 PM
gandolf989
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.
• 04-30-2008, 01:06 PM
PAVB
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.
• 05-02-2008, 02:47 AM
ams-jamali