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

Posted on 2016-11-03
Last Modified: 2016-11-07
I need some help with a query I need to populate a report.
I have data that looks like this:
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
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.
Question by:JDCam
  • 2
  • 2
LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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


Author Comment

Comment Utility
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.
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.

Author Closing Comment

Comment Utility
Works Great Thanks

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now