SQL Query - Oracle 10g - Subract date from next data row

Experts,
I need some help with a query I need to populate a report.
I have data that looks like this:
JOB        START TIME
TASK1   02/11/2016 09:00
TASK2   02/11/2016 09:06
TASK3   02/11/2016 09:08
END      02/11/2016 09:12

Each row has a JobID and a start time. The end time needs to be the start time of the next Job.
My goal is to add a column that contains the total task time
JOB        START TIME        TOTAL TIME
TASK1   02/11/2016 09:00   6.0
TASK2   02/11/2016 09:06   2.0
TASK3   02/11/2016 09:08   4.0
END      02/11/2016 09:12

Obviously I need to subtract Task1 from Task2 to get the time, but how can I write a query that looks at the next row?
any guidance is greatly appreciated.
LVL 1
JDCamAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
LEAD allows you to peak at the next row.

If you want the results in minutes try this:
select job, start_time, (lead(start_time) over(order by start_time)-start_time) * 1440
from yourtable;

Open in new window

0
 
JDCamAuthor Commented:
Sounds great. I will read up on and try it out.
I assume it considers any order by clause to determine what the next row is.
Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>I assume it considers any order by clause to determine what the next row is.

Nope.  It uses the ORDER BY inside the LEAD call.
0
 
JDCamAuthor Commented:
Works Great Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.