Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

How to Calculate Average Daily Time from Start_Time and End_Time fields

user10903866May 20 2014 — edited May 30 2014

I am using ORACLE 11G on Red Hat Linux, I am trying to calculated the average Daily time from multiple transactions for each day. The followign is the querry which shows the time difference between the start_ time and end_time fields.

select TRIP_LOAD_STARTED AS START_TIME,TRIP_LOAD_ENDED AS END_TIME,

ceil(( TRIP_LOAD_EDDT - TRIP_LOAD_STDT) * 1440) " Time Difference Bw Start and End"
from GATE2GATE_ACTIVITY_VW
Where TRIP_LOAD_STARTED >= '01/FEB/14'
AND TRIP_LOAD_ENDED < '03/FEB/14'
ORDER BY TRIP_LOAD_STARTED;

The above querry gives me the output as follows:

Start_Time     End_Time     Time Diff bw Start and End

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14         18

01/FEB/14    01/FEB/14           12

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14           29

02/FEB/14    02/FEB/14         19

02/FEB/14    02/FEB/14         19

02/FEB/14    02/FEB/14         19


It shows two dates for which I want to get the average time in minutes for each date group by the start time.


I am using the following querry which probably is giving me the average of the two days mentioned in where clause


select  floor(avg(( TRIP_LOAD_STARTED - TRIP_LOAD_ENDED) * 1440)) " Average time Minutes "

from gate2gate_activity_vw

Where TRIP_LOAD_STDT >= '01/FEB/14'

AND TRIP_LOAD_STDT < '03/FEB/14';

Average Time in Minutes

29

Can anyone help me to calculated Daily Average time between Start and EndTime.

Kind regards,

mhamidch

This post has been answered by Moazzam on May 21 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 27 2014
Added on May 20 2014
5 comments
2,328 views