Oracle Query help!

Question

Oracle Query help!

Frank He

SSC Journeyman

Points: 79
More actions
May 17, 2009 at 9:54 am

#205010

In aircraft industry, the flight from one place to its destination called leg, but some requests have mulitple stops (legs). I need to caculate the turn time in minutes if the request has mulitple legs. This is the number of minutes between the previous leg "In Time" and the subsequent leg "Out Time" for consecutive legs. Is there any Oracle Analytical Functions get this task done?
The table looks like this:
LEGID NEXTLEGID REQUESTID INTIME OUTTIME
111 0001 7/5/2003 4:12:00 AM 7/5/2003 2:15:00 AM
112 324 0021 7/5/2003 8:00:00 PM 7/5/2003 6:30:00 PM
124 143 0022 7/6/2003 01:18:00 AM 7/5/2003 11:56:00 PM
143 0022 7/6/2003 05:18:00 AM 7/6/2003 03:05:00 AM
324 0021 7/6/2003 10:18:00 AM 7/5/2003 8:27:00 PM
Thank you for your inputs
Regards,
Frank

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Frank He (5/17/2009)
In aircraft industry, the flight from one place to its destination called leg, but some requests have mulitple stops (legs). I need to caculate the turn time in minutes if the request has mulitple legs. This is the number of minutes between the previous leg "In Time" and the subsequent leg "Out Time" for consecutive legs. Is there any Oracle Analytical Functions get this task done?
The table looks like this:
LEGID NEXTLEGID REQUESTID INTIME OUTTIME
111 0001 7/5/2003 4:12:00 AM 7/5/2003 2:15:00 AM
112 324 0021 7/5/2003 8:00:00 PM 7/5/2003 6:30:00 PM
124 143 0022 7/6/2003 01:18:00 AM 7/5/2003 11:56:00 PM
143 0022 7/6/2003 05:18:00 AM 7/6/2003 03:05:00 AM
324 0021 7/6/2003 10:18:00 AM 7/5/2003 8:27:00 PM
Thank you for your inputs
Regards,
Frank

Just subtract the In time from the Out time and reformat the answer to suit your needs. No special analytics required.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frank He SSC Journeyman Points: 79 More actions · Answer 2

Jeff Moden (5/17/2009)
Frank He (5/17/2009)
In aircraft industry, the flight from one place to its destination called leg, but some requests have mulitple stops (legs). I need to caculate the turn time in minutes if the request has mulitple legs. This is the number of minutes between the previous leg "In Time" and the subsequent leg "Out Time" for consecutive legs. Is there any Oracle Analytical Functions get this task done?
The table looks like this:
LEGID NEXTLEGID REQUESTID INTIME OUTTIME
111 0001 7/5/2003 4:12:00 AM 7/5/2003 2:15:00 AM
112 324 0021 7/5/2003 8:00:00 PM 7/5/2003 6:30:00 PM
124 143 0022 7/6/2003 01:18:00 AM 7/5/2003 11:56:00 PM
143 0022 7/6/2003 05:18:00 AM 7/6/2003 03:05:00 AM
324 0021 7/6/2003 10:18:00 AM 7/5/2003 8:27:00 PM
Thank you for your inputs
Regards,
Frank
Just subtract the In time from the Out time and reformat the answer to suit your needs. No special analytics required.

Frank He SSC Journeyman Points: 79 More actions · Answer 3

Jeff,

Thank you for your input. But I think I didn't make it clear.

For Requestid 0021, there are two legids associated with it(112, 324). For each leg, I should find it's intime from previous leg (if it exists). Same for Requestid 0022, there are two legids assosciated with it(124, 143). I think it should group by requestid first, then go from there.

If you click on "Edit", the sample data would be displayed more clear to you.

Thanks,

Frank

Jeff Moden (5/17/2009)
Frank He (5/17/2009)
In aircraft industry, the flight from one place to its destination called leg, but some requests have mulitple stops (legs). I need to caculate the turn time in minutes if the request has mulitple legs. This is the number of minutes between the previous leg "In Time" and the subsequent leg "Out Time" for consecutive legs. Is there any Oracle Analytical Functions get this task done?
The table looks like this:
LEGID NEXTLEGID REQUESTID INTIME OUTTIME
111 0001 7/5/2003 4:12:00 AM 7/5/2003 2:15:00 AM
112 324 0021 7/5/2003 8:00:00 PM 7/5/2003 6:30:00 PM
124 143 0022 7/6/2003 01:18:00 AM 7/5/2003 11:56:00 PM
143 0022 7/6/2003 05:18:00 AM 7/6/2003 03:05:00 AM
324 0021 7/6/2003 10:18:00 AM 7/5/2003 8:27:00 PM
Thank you for your inputs
Regards,
Frank
Just subtract the In time from the Out time and reformat the answer to suit your needs. No special analytics required.

Frank He SSC Journeyman Points: 79 More actions · Answer 4

I would like to have output like this:

LEGID NEXTLEGID REQUESTID previous_legid, Previous_intime, TURN_TIME*

111 0001

112 324 0021

124 143 0022

143 0022 124 7/6/2003 01:18:00 AM 107

324 0021 112 7/5/2003 8:00:00 PM 27

----Turn_Time = (OUTTIME-Previous_intime) * 60*24

For legid 143, TURN_TIME = (7/6/2003 03:05:00 AM - 7/6/2003 01:18:00 AM)*60*24

For legid 324, TURN_TIME = (7/5/2003 8:27:00 PM - 7/5/2003 8:00:00 PM )*60*24

aj-753163 SSC Journeyman Points: 95 More actions · Answer 5

use to_date_function...

(to_date('7/6/2003 03:05:00' , 'mm/dd/yyyy hh24:mi:ss')-to_date('7/6/2003 01:18:00' , 'mm/dd/yyyy hh24:mi:ss'))*60*24