• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

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.
0
JDCam
Asked:
JDCam
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now