Most people think of a “disaster” as something catastrophic and geographically widespread— an earthquake, fire, flood, or tornado, for example. Truth is, the disasters that cost global business the most don’t fit the traditional definition of catastrophe.
Oracle - Difference Between Dates in Different Rows
An example: you need to calculate the processing time between status' for a given invoice:
Transaction_Table
| Invoice_ID | Status | Time_Stamp |
| 1 | Submit | 2011-01-01 7:00:00am |
| 1 | Code | 2011-01-01 9:00:00am |
| 1 | Approve | 2011-01-01 1:00:00pm |
| 1 | Close | 2011-01-02 7:00:00am |
| 3 | Submit | 2011-01-01 7:05:00am |
You may be inclined to do multiple joins back to the transaction_table to get the next status and subtract the dates, ie:
Select invoice_id,
(Invoice2.time_stamp - invoice1.time_stampe) * 24 as coded_time,
(Invoice3.time_stamp – invoice2.time_stamp) * 24 as approved_time,
(Invoice4.time_stamp – invoice3.time_stamp) * 24 as closed_time
From Transaction_table invoice1
Left join Transaction_table invoice2 on invoice1.invoice_id = invoice2.invoice_id
And invoice2.status = ‘Code’
Left join Transaction_table invoice3 on invoice1.invoice_id = invoice3.invoice_id
And invoice3.status = ‘Approve’
Left join Transaction_table invoice4 on invoice1.invoice_id = invoice4.invoice_id
And invoice4.status = ‘Close’
Where invoice1.status = ‘Submit’
| Invoice_ID | Coded_time | Approved_time | Closed_time |
| 1 | 2 | 4 | 18 |
| 3 | Null | Null | null |
Oracle has provided two functions that can help alleviate these unnecessary joins. The Lead() and Lag() functions were introduced to Oracle in version 8.1.6 making this query much simpler.
The Lead() function allows us to get the next row relative to the current row and the Lag() function allows us to get the previous row relative to the current row.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
We can retrieve the same information in a more normalized form allowing the query to handle more status’ as they are added (ie, multiple approvals etc) and eliminate the joins.
Select invoice_id,
status
(time_stamp - lag(time_stamp,1,null) over (partition by invoice_id order by time_stamp)) * 24 as time
from transaction_table
| Invoice_id | Status | Time |
| 1 | Submit | Null |
| 1 | Code | 2 |
| 1 | Approve | 3 |
| 1 | Close | 18 |
| 3 | Submit | Null |
Follow Us