How can I subtract two row's values within DIFFERENT columns using SQL

Question

I need help calculating downtime in between processes. It needs to be grouped by IMPORTID and then IMPORTREQUESTID. IMPORTREQUESTID corresponds to different phases within the IMPORTID request. So, for example in below data set I need my SQL query to find the highest or Latest ENDDATE amongst IMPORTREQUESTIDs and then subtract STARTDATE (lowest) of next row from it or rather min(STARTDATE) of next cluster of IMPORTREQUESTID. I've already sorted my query to sort IMPORT ID and IMPORTREQUEST ID in descending order. Should I use "CONNECT BY"? What would be the best method to group by and find highest ENDDATE in rows of IMPORTID and IMPORTREQUESTID and then subtract the lowest STARTDATE from it, within next immediate row or cluster of IMPORTREQUESTIDs? Basically, I am trying to calculate the gap time between when a process is finished and when next process starts. See below table for sample data:

IMPORTID IMPORTREQUESTID    STARTDATE       ENDDATE
1156     63833              4/23/2017 18:18 4/23/2017 18:18
1156     63833              4/23/2017 18:18 4/23/2017 18:18
1156     63832              4/23/2017 17:56 4/23/2017 17:57
1156     63832              4/23/2017 17:56 4/23/2017 17:57
1156     63832              4/23/2017 17:56 4/23/2017 17:57

Expected result: Would be for the above query to return to me the SUM of all differences obtained for entire set of rows and display per IMPORTID in the end:

ImportID  TOTAL Downtime        
1156          21 mins

OR even better:

a detailed breakdown with the gaps in seconds between each unique IMPORTREQUESTID (Enddate of above row and startdate of below row) as explained and return the following unique rows (max ENDDATE of a phase and the proceeding min(startdate) of next phase):

IMPORTID IMPORTREQUESTID  STARTDATE        ENDDATE          DIFF
1156      63833           4/23/2017 18:18  4/23/2017 18:18  21 mins
1156      63832           4/23/2017 17:56  4/23/2017 17:57

Answer 1

Something like this? I don't fully understand the question yet.. especially where the 61 sec comes from I get 21 min. I also don't know why you have repeated data in your example.... So I removed it using distinct.

Two key points here.

1) LEAD is a window function that lets us look ahead to the next record in the order sequence defined. We could also "PARTITION" so that each series doesn't look ahead unless the ImportId and ImportRequestID match so

Round((EndDate-LEAD(EndDate) over (order by ImportID, ImportRequestID DESC))*60*24)
would become

Round((EndDate-LEAD(EndDate) over (PARTITION BY IMPORTID order by ImportRequestID DESC))*60*24)

2) I used distinct to eliminate what appear to be duplicate records; but I doubt your dataset really has duplicates so it may not be needed; or perhaps you have a join incomplete which is causing duplicates.

 With CTE (IMPORTID, ImportRequestID, StartDate, EndDate) as (
SELECT 1156,     63833, to_date('4/23/2017 18:18','MM/DD/YYYY HH24:MI'), to_date('4/23/2017 18:18','MM/DD/YYYY HH24:MI') FROM DUAL UNION ALL
SELECT 1156,     63833, to_date('4/23/2017 18:18','MM/DD/YYYY HH24:MI'), to_date('4/23/2017 18:18','MM/DD/YYYY HH24:MI') FROM DUAL UNION ALL
SELECT 1156,     63832, to_date('4/23/2017 17:56','MM/DD/YYYY HH24:MI'), to_date('4/23/2017 17:57','MM/DD/YYYY HH24:MI') FROM DUAL UNION ALL
SELECT 1156,     63832, to_date('4/23/2017 17:56','MM/DD/YYYY HH24:MI'), to_date('4/23/2017 17:57','MM/DD/YYYY HH24:MI') FROM DUAL UNION ALL
SELECT 1156,     63832, to_date('4/23/2017 17:56','MM/DD/YYYY HH24:MI'), to_date('4/23/2017 17:57','MM/DD/YYYY HH24:MI') FROM DUAL)

SELECT ImportID
     , ImportRequestID
     , to_char(StartDate,'MM/DD/YYYY HH24:MI') StartDate
     , to_char(EndDate,'MM/DD/YYYY HH24:MI') EndDate
     , Round((EndDate-LEAD(EndDate) over (order by ImportID, ImportRequestID DESC))*60*24) as Minutediff 
FROM (SELECT DISTINCT ImportID
     , ImportRequestID
     , StartDate
     , EndDate
     From CTE) B

Answer 2

You could order the data by the importid and importrequestid in a way that ensures they are in the correct chronological order. Then as noted by xQbert, use lead() or lag() to make columns for your ending datetime and beginning datetime of next process.

lag(enddate, 1) over (order by importrequestid) as priorend

Then use something like the following function to find differnce.

create or replace function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)
       return number is total_secs number;
       diff interval day(9) to second(6);
   begin
   diff := ts2 - ts1;
   total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);

   return total_secs;
end timestamp_diff_in_seconds;

Then call the function example...

select timestamp_diff_in_seconds(priorend, startdate) as downtime.