Home » SQL & PL/SQL » SQL & PL/SQL » Time difference between 2 rows (oracle 12c)
Time difference between 2 rows [message #671682] |
Wed, 12 September 2018 15:23 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Hi, I am trying to get the time difference between 2 rows for the same Trans_id, that has 2 different trans_cd
when the transaction start TRANS_CD='SVP08' and when the transaction completes TRans_cd='SVP01'
basically I need the output as example TRANS_ID=100000053487855 and difference=2 secs.
sample create table, insert statement and select is paste, Please advice.
CREATE TABLE TRANS_LOG_INFO
(
TRANS_CD VARCHAR2(10 BYTE) NOT NULL,
DT_TIME DATE NOT NULL,
TRANS_ID VARCHAR2(35 BYTE)) ;
SET DEFINE OFF;
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487850', 'SVP01', TO_DATE('09/12/2018 10:30:36', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487850', 'SVP08', TO_DATE('09/12/2018 10:30:36', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487855', 'SVP08', TO_DATE('09/12/2018 10:31:40', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487855', 'SVP01', TO_DATE('09/12/2018 10:31:42', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487865', 'SVP01', TO_DATE('09/12/2018 10:33:06', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487865', 'SVP08', TO_DATE('09/12/2018 10:33:06', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487880', 'SVP01', TO_DATE('09/12/2018 10:38:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487880', 'SVP08', TO_DATE('09/12/2018 10:38:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487895', 'SVP08', TO_DATE('09/12/2018 10:38:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487895', 'SVP01', TO_DATE('09/12/2018 10:38:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487910', 'SVP01', TO_DATE('09/12/2018 10:38:28', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487910', 'SVP08', TO_DATE('09/12/2018 10:38:28', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487920', 'SVP08', TO_DATE('09/12/2018 10:38:31', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487920', 'SVP01', TO_DATE('09/12/2018 10:38:32', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487925', 'SVP01', TO_DATE('09/12/2018 10:38:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487925', 'SVP08', TO_DATE('09/12/2018 10:38:33', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487940', 'SVP01', TO_DATE('09/12/2018 10:38:38', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487940', 'SVP08', TO_DATE('09/12/2018 10:38:38', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487950', 'SVP08', TO_DATE('09/12/2018 10:38:51', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487950', 'SVP01', TO_DATE('09/12/2018 10:38:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487955', 'SVP01', TO_DATE('09/12/2018 10:38:59', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_LOG_INFO
(TRANS_ID, TRANS_CD, DT_TIME)
Values
('100000053487955', 'SVP08', TO_DATE('09/12/2018 10:38:59', 'MM/DD/YYYY HH24:MI:SS'));
commit;
SELECT TRANS_ID, TRANS_CD, DT_TIME FROM TRANS_LOG_INFO WHERE TRANS_CD in ('SVP08','SVP01') AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS')
100000053487850 SVP01 9/12/2018 10:30:36 AM
100000053487850 SVP08 9/12/2018 10:30:36 AM
100000053487855 SVP08 9/12/2018 10:31:40 AM
100000053487855 SVP01 9/12/2018 10:31:42 AM
100000053487865 SVP01 9/12/2018 10:33:06 AM
100000053487865 SVP08 9/12/2018 10:33:06 AM
100000053487880 SVP01 9/12/2018 10:38:03 AM
100000053487880 SVP08 9/12/2018 10:38:03 AM
For some rows they may not be any difference in time between trans start and end then just the trans_id and difference =0.
Thanks
|
|
|
|
Re: Time difference between 2 rows [message #671684 is a reply to message #671683] |
Wed, 12 September 2018 16:14 |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Thanks for looking into it,
there is only 1 Date column,
sorry not sure what you mean by date1 minus date2.
basically I am trying to get the difference in time between 2 rows for the same Trans_id for trans_cd SVP01-SVP08,
|
|
|
Re: Time difference between 2 rows [message #671685 is a reply to message #671684] |
Wed, 12 September 2018 16:47 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
use common table expression to create 2 datasets, 1 for svp01 and 1 for spv08
Join the two ctes on the trans id
subtract one dt_time from the other and convert the difference to seconds.
with t1 as (SELECT TRANS_ID, TRANS_CD, DT_TIME
FROM TRANS_LOG_INFO
WHERE TRANS_CD = 'SVP08'
AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS'))
, t2 as (SELECT TRANS_ID, TRANS_CD, DT_TIME
FROM TRANS_LOG_INFO
WHERE TRANS_CD = 'SVP01'
AND DT_TIME >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS'))
select *
from t1
join t2
on trans_id
[Updated on: Wed, 12 September 2018 16:58] Report message to a moderator
|
|
|
|
Re: Time difference between 2 rows [message #671689 is a reply to message #671682] |
Thu, 13 September 2018 01:54 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Another solution -pdby1>
pdby1> select trans_id,(max(dt_time)-min(dt_time))*86400 from trans_log_info group by trans_id order by trans_id;
TRANS_ID (MAX(DT_TIME)-MIN(DT_TIME))*86400
----------------------------------- ---------------------------------
100000053487850 0
100000053487855 2
100000053487865 0
100000053487880 0
100000053487895 1
100000053487910 0
100000053487920 1
100000053487925 0
100000053487940 0
100000053487950 1
100000053487955 0
11 rows selected.
pdby1> How does it compare to Pablo's?
|
|
|
|
Re: Time difference between 2 rows [message #671691 is a reply to message #671685] |
Thu, 13 September 2018 04:11 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
CTE seems unnecessary for something this simple:
SELECT tlie.trans_id, (tlie.dt_time - tlis.dt_time) * (24 * 60 * 60) AS time_diff
FROM trans_log_info tlie
JOIN trans_log_info tlis ON tlie.trans_id = tlis.trans_id
WHERE tlis.trans_cd = 'SVP08'
AND tlie.trans_cd = 'SVP01'
AND tlis.dt_time >= to_date('09/12/2018 10:06:00','MM/DD/YYYY HH24:MI:SS')
ORDER BY 1;
|
|
|
|
Re: Time difference between 2 rows [message #671757 is a reply to message #671750] |
Tue, 18 September 2018 08:48 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The following query uses the Lag Function
SELECT Trans_id,
Trans_cd,
Dt_time,
(Dt_time - (LAG(Dt_time, 1)
OVER (PARTITION BY Trans_id
ORDER BY Trans_id, CASE WHEN Trans_cd = 'SVP08' THEN 1 ELSE 2 END
))) * (60 * 60 * 24)
Seconds
FROM Trans_log_info
WHERE Trans_cd IN ('SVP08', 'SVP01')
AND Dt_time >= TO_DATE('09/12/2018 10:06:00', 'MM/DD/YYYY HH24:MI:SS')
ORDER BY Trans_id, CASE WHEN Trans_cd = 'SVP08' THEN 1 ELSE 2 END;
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 06:45:43 CDT 2024
|