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