?
Solved

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

Posted on 2016-11-03
4
Medium Priority
?
106 Views
Last Modified: 2016-11-07
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.
0
Comment
Question by:JDCam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41872166
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
 
LVL 1

Author Comment

by:JDCam
ID: 41872230
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872235
>>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
 
LVL 1

Author Closing Comment

by:JDCam
ID: 41877074
Works Great Thanks
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 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