Date / time difference in Oracle for one field on different rows
I have below view, I need to do to get ActionDate field date difference between every 2 records having same Vehicle AND OrderCode, how can I achieve this in Oracle database.
Also considering that the deducted dates must be those with Mode O - Mode I
I need to get a list of differences to get the average of this time.
Thanks for the help.
+3
source to share
4 answers
You can use the LAG () OVER () analytic function to get the difference between 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 create the sample data , in your case you need to use the 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 put TO_CHAR just for demonstration, the desired result is a DIFF column . As for MODE , you can add it to the filter predicate.
+3
source to share
Use GROUP BY and subtract dates as part of an optional 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
0
source to share
You can try with this (not tested, so may have a 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
0
source to share
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
-1
source to share