Date/Time Difference in Oracle for same field in different rows

Question

I have the below view, what I need to do is to get the date difference of the field ActionDate between each 2 records having the same Vehicle AND OrderCode, how can I achieve this in Oracle database.

Also taking into consideration that the dates subtracted should be the one having the Mode O - Mode I

I need to get the list of the differences in order to get the average of that time.

Thanks for helping.

Claiborne

I appreciate if you give me the reason behind the downvotes, it is a complex query and something that can't be found in a google search, so why the downvotes?? – Claiborne

Answer 1

You could use the analytic LAG() OVER() function to get the difference between the dates.

For example,

SQL> WITH t AS
  2  (
  3    select 'O' as "MODE", 'V1234567890' as  Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 16:34:35','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  4    union all
  5    select 'I' as "MODE", 'V1234567890' as  Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 15:27:09','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  6    union all
  7    select 'O' as "MODE", 'V2987654321' as  Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 14:03:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  8    union all
  9    select 'I' as "MODE", 'V2987654321' as  Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 13:47:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
 10    union all
 11    select 'O' as "MODE", 'V2987654321' as  Vehicle, '1411185798' as OrderCode, to_date('2014-11-20 01:40:58','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
 12    union all
 13    SELECT 'I' AS "MODE", 'V2987654321' AS  Vehicle, '1411185798' AS OrderCode, to_date('2014-11-20 00:47:02','yyyy-mm-dd hh24:mi:ss') AS ActionDate FROM dual
 14  )
 15  SELECT "MODE",
 16    Vehicle,
 17    OrderCode,
 18    TO_CHAR(ActionDate,'yyyy-mm-dd hh24:mi:ss') dt,
 19    TO_CHAR(LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate),'yyyy-mm-dd hh24:mi:ss') lag_dt,
 20    ActionDate - LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate) diff
 21  FROM t;

M VEHICLE     ORDERCODE  DT                  LAG_DT                    DIFF
- ----------- ---------- ------------------- ------------------- ----------
I V1234567890 1411196232 2014-11-19 15:27:09
O V1234567890 1411196232 2014-11-19 16:34:35 2014-11-19 15:27:09 .046828704
I V2987654321 1411185798 2014-11-20 00:47:02
O V2987654321 1411185798 2014-11-20 01:40:58 2014-11-20 00:47:02 .037453704
I V2987654321 1411206614 2014-11-20 13:47:02
O V2987654321 1411206614 2014-11-20 14:03:02 2014-11-20 13:47:02 .011111111

6 rows selected.

SQL>

NOTE: The WITH clause is to build the sample data, in your case you need to use your actual table_name:

SELECT "MODE",
  Vehicle,
  OrderCode,
  TO_CHAR(ActionDate,'yyyy-mm-dd hh24:mi:ss') dt,
  TO_CHAR(LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate),'yyyy-mm-dd hh24:mi:ss') lag_dt,
  ActionDate - LAG(ActionDate) OVER(PARTITION BY Vehicle,OrderCode ORDER BY Vehicle, ActionDate) diff
FROM your_table;

I have put the TO_CHAR just for demonstration purpose, your desired output is the DIFF column. Regarding the MODE, you could add it to the filter predicate.

Larkspur

Answer 2

Use GROUP BY and subtract the dates within a sub query

SELECT t.IO_SEQ, t.Vehicle, t.OrderCode, (SELECT MAX(t2.ActionDate) FROM table t2 WHERE t.IO_SEQ = t2.IO_SEQ) - (SELECT MIN(t2.ActionDate) FROM table t2 WHERE t.IO_SEQ = t2.IO_SEQ) AS ActionDiff
FROM table t
GROUP BY t.IO_SEQ, t.Vehicle, t.OrderCode

Demolition

io_seq shouldn't be in the group by clause, as it appears to be unique – Algae

@ammoQ It wouldn't run without it and i don't think there is a negative to having it there? – Demolition

thanks but the ActionDiff is always: +00 00:00:00.000000 – Claiborne

@Demolition In this case, it defeats the whole purpose of grouping records – Algae

Answer 3

You can try with this (is not tested so it may have some syntax error). The idea is to join the table with itself :

select OMode.Vehicle, OMode.OrderCode, OMode.ActionDate-IMode.ActionDate 
from 
    (select Vehicle, OrderCode, ActionDate from table where Mode='O' ) OMode, 
    (select Vehicle, OrderCode, ActionDate from table where Mode='I' ) IMode

where OMode.Vehicle = IMode.Vehicle and OMode.OrderCode = IMode.OrderCode

Compony

Answer 4

You can use MAX and GROUP BY as in the following query:

with src as 
(
  select 'O' as "MODE", 'V1234567890' as  Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 16:34:35','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  union all
  select 'I' as "MODE", 'V1234567890' as  Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 15:27:09','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  union all
  select 'O' as "MODE", 'V2987654321' as  Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 14:03:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  union all
  select 'I' as "MODE", 'V2987654321' as  Vehicle, '1411206614' as OrderCode, to_date('2014-11-20 13:47:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  union all
  select 'O' as "MODE", 'V2987654321' as  Vehicle, '1411185798' as OrderCode, to_date('2014-11-20 01:40:58','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
  union all
  select 'I' as "MODE", 'V2987654321' as  Vehicle, '1411185798' as OrderCode, to_date('2014-11-20 00:47:02','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual
)

select Vehicle,OrderCode, max(case when "MODE" = 'O' then ActionDate end) as MODE_O,  max(case when "MODE" = 'I' then ActionDate end) as MODE_I from src
group by Vehicle,OrderCode

Added time difference:

select Vehicle,OrderCode, max(case when "MODE" = 'O' then ActionDate end) as MODE_O,  max(case when "MODE" = 'I' then ActionDate end) as MODE_I, max(case when "MODE" = 'O' then ActionDate end) - max(case when "MODE" = 'I' then ActionDate end) as time_difference from src
group by Vehicle,OrderCode

Rexanna

Added time difference – Rexanna